I am trying to create a budgetting system for my company. The requirements from accounting states that I should get the previous balance then add it to the received amount. Subtract the sum of prev balance and received amount from the withdrawals to get the Balance end. Below is my stored procedure:
- ALTER PROCEDURE [dbo].[fin_cash_flow_summary_LAK]
-
- @DateStart nvarchar(50),
- @DateEnd nvarchar(50)
- AS
- BEGIN
-
-
- SET NOCOUNT ON;
-
-
- SELECT a.BankCode,
- a.BankAcctType,
- a.BankInformation,
- a.CurrencyType,
- (
- SELECT SUM(ISNULL(b.CreditKip,0) - ISNULL(b.DebitKip,0))
- FROM fin_Cash_Flow_Master b
- WHERE a.BankInformation = b.BankInformation AND a.CurrencyType='LAK' AND a.BankAcctType <> 'Fixed' AND b.TransactionDate < @DateStart
- ) AS PreviousBalance,
- SUM(CreditKip) AS [Receive],
- SUM(DebitKip) AS Withdraw,
- (SELECT SUM(ISNULL(CreditKip, 0) - ISNULL(DebitKip, 0)) FROM dbo.fin_Cash_Flow_Master AS b WHERE (a.BankInformation = BankInformation)
- AND (a.CurrencyType = 'LAK') AND (a.BankAcctType <> 'Fixed') AND (TransactionDate < @DateStart)) + SUM(CreditKip) - SUM(DebitKip) AS BalanceEnd
- FROM dbo.fin_Cash_Flow_Master a
- WHERE a.TransactionDate BETWEEN @DateStart AND @DateEnd AND a.CurrencyType = 'LAK' AND a.BankAcctType <> 'Fixed'
- GROUP BY BankCode, BankAcctType, BankInformation, CurrencyType, BankAccountNo
- END
With that query, I get results for previous balance before the start date if there is transaction on the date provided. But When I query it to the current day, I got nothing. Supposed to be, I am getting the previous balance. But, there is nothing to show. Please advice. Thank you so much