Friday, 12 April 2013

Scalar function of sql

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

RETURNS DECIMAL(18,2)

 AS 

     BEGIN
      DECLARE @CREDIT_AMOUNT DECIMAL(18,2)
        SET @CREDIT_AMOUNT=0
        SET @CREDIT_AMOUNT= (SELECT             
               SUM(Accounts.VoucherDetails.CreditAmount) AS CrAmount
               
                    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)
        RETURN @CREDIT_AMOUNT
    END

No comments:

Post a Comment