Jes Sie

Jes Sie

  • 704
  • 1.2k
  • 265.1k

Get Previous Balance, Received, Withdrawal and End Bal in SQL SERVER

Jul 6 2020 1:34 AM
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:
 
  1. ALTER PROCEDURE [dbo].[fin_cash_flow_summary_LAK] --'2020-07-06', '2020-07-06'  
  2.     -- Add the parameters for the stored procedure here  
  3.     @DateStart nvarchar(50),  
  4.     @DateEnd nvarchar(50)  
  5. AS  
  6. BEGIN  
  7.     -- SET NOCOUNT ON added to prevent extra result sets from  
  8.     -- interfering with SELECT statements.  
  9.     SET NOCOUNT ON;  
  10.   
  11.     -- Insert statements for procedure here  
  12.     SELECT  a.BankCode,   
  13.         a.BankAcctType,   
  14.         a.BankInformation,   
  15.         a.CurrencyType,  
  16.         (  
  17.         SELECT SUM(ISNULL(b.CreditKip,0) - ISNULL(b.DebitKip,0))  
  18.         FROM fin_Cash_Flow_Master b  
  19.         WHERE a.BankInformation = b.BankInformation AND a.CurrencyType='LAK' AND a.BankAcctType <> 'Fixed' AND  b.TransactionDate < @DateStart  
  20.         )   AS PreviousBalance,  
  21.         SUM(CreditKip) AS [Receive],   
  22.         SUM(DebitKip) AS Withdraw,  
  23.         (SELECT SUM(ISNULL(CreditKip, 0) - ISNULL(DebitKip, 0)) FROM dbo.fin_Cash_Flow_Master AS b WHERE (a.BankInformation = BankInformation)   
  24.         AND (a.CurrencyType = 'LAK'AND (a.BankAcctType <> 'Fixed'AND (TransactionDate < @DateStart)) + SUM(CreditKip) - SUM(DebitKip) AS BalanceEnd  
  25.     FROM    dbo.fin_Cash_Flow_Master a  
  26.     WHERE a.TransactionDate BETWEEN @DateStart AND @DateEnd AND a.CurrencyType = 'LAK' AND a.BankAcctType <> 'Fixed'  
  27.     GROUP BY BankCode, BankAcctType, BankInformation, CurrencyType, BankAccountNo  
  28. 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

Answers (3)