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

Jes Sie

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] --'2020-07-06', '2020-07-06'
-- Add the parameters for the stored procedure here
@DateStart nvarchar(50),
@DateEnd nvarchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
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

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Laxmidhar Sahoo6y ago
hi
I am not aware about your table .Please details show .So that I can analysis and may try to solve.

Here there is no DML statements .So no necessary to worry

Thanks and Regards
Jes Sie6y ago
hello Lax, I tried the code that you gave me but still nothing. see pix below (sorry, I immediately pasted it here. there is an exception when I tried to upload my image)
Laxmidhar Sahoo6y ago
Hi
Select all the fields with the
ALTER PROCEDURE [dbo].[fin_cash_flow_summary_LAK] --'2020-07-06', '2020-07-06'
    -- Add the parameters for the stored procedure here
    @DateStart nvarchar(50),
    @DateEnd nvarchar(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    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(b.CreditKip, 0) - ISNULL(b.DebitKip, 0)) FROM dbo.fin_Cash_Flow_Master AS b WHERE (a.BankInformation = b.BankInformation)
        AND (b.CurrencyType = 'LAK') AND (b.BankAcctType <> 'Fixed') AND (b.TransactionDate < @DateStart)) + SUM(b.CreditKip) - SUM(b.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 a.BankCode, a.BankAcctType, a.BankInformation, a.CurrencyType, a.BankAccountNo
END
alish name