Friday, 12 April 2013

Scalar function of sql

ALTER  FUNCTION [Accounts].[userDefinedFunction_Group_Debit_Amount]
(
@companyId UNIQUEIDENTIFIER,
@financialyearId UNIQUEIDENTIFIER,
@groupId UNIQUEIDENTIFIER,
@fromDate Datetime,
@toDate Datetime
)

RETURNS DECIMAL(18,2)

 AS 

     BEGIN
      DECLARE @DEBIT_AMOUNT DECIMAL(18,2)
        SET @DEBIT_AMOUNT=0
        SET @DEBIT_AMOUNT= (SELECT    
               SUM(Accounts.VoucherDetails.DebitAmount) AS DrAmount          
               
                    FROM   Accounts.LedgerGroups INNER JOIN
                            Accounts.Ledgers ON Accounts.LedgerGroups.Id = Accounts.Ledgers.GroupId INNER JOIN
                            Accounts.VoucherDetails ON Accounts.Ledgers.Id = Accounts.VoucherDetails.LedgerId INNER JOIN
                            Accounts.Vouchers ON Accounts.VoucherDetails.VoucherId = Accounts.Vouchers.Id
                    WHERE   Accounts.Vouchers.VoucherDate BETWEEN @fromDate AND @toDate and Accounts.LedgerGroups.Id = @groupId
                          AND (Accounts.Vouchers.FinancialYearId = @financialYearId) AND (Accounts.Vouchers.CompanyId = @companyId)
                    GROUP BY   Accounts.LedgerGroups.Id, Accounts.LedgerGroups.GroupName, Accounts.Vouchers.FinancialYearId,
                               Accounts.Vouchers.CompanyId)--you see in design mode
        RETURN @DEBIT_AMOUNT
    END

No comments:

Post a Comment