Friday, 12 April 2013

Scalar function of sql

ALTER  FUNCTION [Accounts].[userDefinedFunction_Account_Group_OpeningBalance_Amount]
(
@companyId uniqueidentifier,
@financialyearId uniqueidentifier,
@groupId uniqueidentifier,
@fromDate Datetime
)

RETURNS DECIMAL(18,2)

 AS 

     BEGIN
      DECLARE @OPENINGBALANCE_AMOUNT DECIMAL(18,2),@DEBIT_AMOUNT DECIMAL(18,2),@CREDIT_AMOUNT DECIMAL(18,2)

        SET @OPENINGBALANCE_AMOUNT=0
        SET @DEBIT_AMOUNT=0
        SET @CREDIT_AMOUNT=0

        SET @OPENINGBALANCE_AMOUNT=(SELECT ISNULL(SUM(OBAmount),0) FROM Accounts.view_Account_Group_OpeningBalance WHERE GroupId=@groupId AND FinancialYearId = @financialYearId AND CompanyId = @companyId)
        SET @DEBIT_AMOUNT = (SELECT ISNULL(SUM([DebitAmount]-[CreditAmount]) ,0) FROM Accounts.view_Account_Group_DebitAmount_CreditAmount where VoucherDate < @fromDate and [GroupId] =@groupId AND FinancialYearId = @financialYearId AND CompanyId = @companyId)

        SET   @OPENINGBALANCE_AMOUNT=(@OPENINGBALANCE_AMOUNT+@DEBIT_AMOUNT)-@CREDIT_AMOUNT

        RETURN @OPENINGBALANCE_AMOUNT
    END

No comments:

Post a Comment