Getting Cash-flow-statement

SqlServer, SqlServer Useful functions

Introduction


    1. Pulls the opening balance for "Cash-In-Hand" from a separate table.
    2. Calculates monthly net cash movement (Credits - Debits).
    3. Computes the running cash balance.
    4. Displays the opening balance as a line item under Receipts.

    Let’s walk through how to implement this step by step.

Description


Step-by-Step Plan

๐Ÿ”น Step 1: Get Opening Balance from AccountHierarchyNew

  • Filter for Category = 'Cash-In-Hand'
  • Use TransactionDate = '1st April' of the current financial year

 

DECLARE @OpeningBalance DECIMAL(18,2); SELECT @OpeningBalance = SUM(TransactionNetAmount) FROM AccountHierarchyNew WHERE Category = 'Cash-In-Hand' AND TransactionDate = DATEFROMPARTS(YEAR(GETDATE()), 4, 1);

 

 

๐Ÿ”น Step 2: Calculate Net Cash Movement from VoucherDetails

  • Filter for Receipt_PaymentMode = 'Cash'
  • Use TransactionType = 'Credit' and 'Debit'
  • Restrict to the month range (@FromDate to @ToDate)

 

DECLARE @CashMovement DECIMAL(18,2); SELECT @CashMovement = SUM(CASE WHEN TransactionType = 'Credit' THEN TransactionNetAmount ELSE 0 END) - SUM(CASE WHEN TransactionType = 'Debit' THEN TransactionNetAmount ELSE 0 END) FROM VoucherDetails WHERE Receipt_PaymentMode = 'Cash' AND (@FromDate IS NULL OR ReceiptDate >= @FromDate) AND (@ToDate IS NULL OR ReceiptDate <= @ToDate);

๎ท™๎ทš

 

 

๐Ÿ”น Step 3: Compute Cash Balance

 

DECLARE @CashBalance DECIMAL(18,2); SET @CashBalance = ISNULL(@OpeningBalance, 0) + ISNULL(@CashMovement, 0);

 

 

๐Ÿ”น Step 4: Inject Opening Balance into Receipts

We’ll insert a synthetic row into the #BaseData temp table before the rest of the logic:

 

INSERT INTO #BaseData (Section, Category, Account, Amount) VALUES ('Receipts', 'Cash', 'Opening Balance', @CashBalance);

 

 

๐Ÿงฉ Final Integration Snippet

Here’s how you’d plug this into your existing procedure:

 

-- Step 1: Opening Balance DECLARE @OpeningBalance DECIMAL(18,2); SELECT @OpeningBalance = SUM(TransactionNetAmount) FROM AccountHierarchyNew WHERE Category = 'Cash-In-Hand' AND TransactionDate = DATEFROMPARTS(YEAR(GETDATE()), 4, 1); -- Step 2: Cash Movement DECLARE @CashMovement DECIMAL(18,2); SELECT @CashMovement = SUM(CASE WHEN TransactionType = 'Credit' THEN TransactionNetAmount ELSE 0 END) - SUM(CASE WHEN TransactionType = 'Debit' THEN TransactionNetAmount ELSE 0 END) FROM VoucherDetails WHERE Receipt_PaymentMode = 'Cash' AND (@FromDate IS NULL OR ReceiptDate >= @FromDate) AND (@ToDate IS NULL OR ReceiptDate <= @ToDate); -- Step 3: Cash Balance DECLARE @CashBalance DECIMAL(18,2); SET @CashBalance = ISNULL(@OpeningBalance, 0) + ISNULL(@CashMovement, 0); -- Step 4: Inject into Receipts INSERT INTO #BaseData (Section, Category, Account, Amount) VALUES ('Receipts', 'Cash', 'Opening Balance', @CashBalance);

๎ท›๎ทœ๎ท™๎ทš

 

 

๐Ÿงพ Result

This will ensure that your Receipts section includes a row like:

 

Category

Account

Amount

Cash

Opening Balance

12345.67

 

Would you like to also show the closing balance for the month as a separate row under Payments or as a summary line?

A Final Version including Opening Balance as a Row  On REceipt Side and Clsoing Balance on Payement Side 

ALTER PROCEDURE [dbo].[GetCategoryAccountSummary]
    @FromDate DATE = NULL,
    @ToDate DATE = NULL
AS
BEGIN
    -- Step 0: Normalize date range
    IF @FromDate IS NULL SET @FromDate = DATEFROMPARTS(YEAR(GETDATE()), 4, 1);
    IF @ToDate IS NULL SET @ToDate = EOMONTH(GETDATE());
 
    -- Step 1: Get Opening Balance from AccountHierarchyNew
    DECLARE @OpeningBalance DECIMAL(18,2);
    SELECT @OpeningBalance = SUM(TransactionNetAmount)
    FROM AccountHierarchyNew
    WHERE Category = 'Cash-In-Hand'
      AND TransactionDate = DATEFROMPARTS(YEAR(@FromDate), 4, 1);
 
    -- Step 2: Calculate Net Cash Movement from VoucherDetails
    DECLARE @CashMovement DECIMAL(18,2);
    SELECT @CashMovement = 
        SUM(CASE WHEN TransactionType = 'Credit' THEN TransactionNetAmount ELSE 0 END) -
        SUM(CASE WHEN TransactionType = 'Debit' THEN TransactionNetAmount ELSE 0 END)
    FROM VoucherDetails
    WHERE Receipt_PaymentMode = 'Cash'
      AND ReceiptDate >= @FromDate
      AND ReceiptDate <= @ToDate;
 
    -- Step 3: Compute Cash Balance
    DECLARE @CashBalance DECIMAL(18,2);
    SET @CashBalance = ISNULL(@OpeningBalance, 0) + ISNULL(@CashMovement, 0);
 
    -- Step 4: Prepare base data
    CREATE TABLE #BaseData (
        Section NVARCHAR(20),
        Category NVARCHAR(100),
        Account NVARCHAR(100),
        Amount DECIMAL(18, 2)
    );
 
    -- Step 5: Insert actual voucher data
    INSERT INTO #BaseData (Section, Category, Account, Amount)
    SELECT  
        CASE 
            WHEN Prefix IN ('R', 'A') THEN 'Receipts'
            WHEN Prefix = 'V' THEN 'Payments'
        END AS Section,
        Receipt_PaymentMode AS Category,
        AccountType AS Account,
        TransactionNetAmount AS Amount
    FROM VoucherDetails
    WHERE 
        Prefix IN ('R', 'A', 'V')
        AND ReceiptDate >= @FromDate
        AND ReceiptDate <= @ToDate;
 
    -- Step 6: Inject synthetic opening balance row under Receipts
    INSERT INTO #BaseData (Section, Category, Account, Amount)
    VALUES ('Receipts', 'Cash', 'Opening Balance', @CashBalance);
 
    -- Step 7: Inject synthetic closing balance row under Payments
    INSERT INTO #BaseData (Section, Category, Account, Amount)
    VALUES ('Payments', 'Cash', 'Closing Balance', @CashBalance);
 
    -- Step 8: Prepare Receipts and Payments with row numbers
    WITH ReceiptsCTE AS (
        SELECT 
            ROW_NUMBER() OVER (ORDER BY Category, Account) AS RowNum,
            Category AS ReceiptCategory,
            Account AS ReceiptAccount,
            SUM(Amount) AS Receipts
        FROM #BaseData
        WHERE Section = 'Receipts'
        GROUP BY Category, Account
    ),
    PaymentsCTE AS (
        SELECT 
            ROW_NUMBER() OVER (ORDER BY Category, Account) AS RowNum,
            Category AS PaymentCategory,
            Account AS PaymentAccount,
            SUM(Amount) AS Payments
        FROM #BaseData
        WHERE Section = 'Payments'
        GROUP BY Category, Account
    ),
    Combined AS (
        SELECT 
            R.ReceiptCategory,
            R.ReceiptAccount,
            R.Receipts,
            P.PaymentCategory,
            P.PaymentAccount,
            P.Payments
        FROM ReceiptsCTE R
        FULL OUTER JOIN PaymentsCTE P ON R.RowNum = P.RowNum
    )
 
    -- Step 9: Final output with grand total row
    SELECT *
    FROM (
        SELECT 
            ReceiptCategory,
            ReceiptAccount,
            Receipts,
            PaymentCategory,
            PaymentAccount,
            Payments,
            0 AS SortOrder
        FROM Combined
 
        UNION ALL
 
        SELECT 
            'TOTAL',
            NULL,
            SUM(Receipts),
            'TOTAL',
            NULL,
            SUM(Payments),
            1 AS SortOrder
        FROM Combined
    ) AS FinalResult
    ORDER BY SortOrder, ReceiptCategory, ReceiptAccount;
 
    DROP TABLE #BaseData;
END;
Recents
Getting Cash-flow-statement
GetMonthlyCashFlowSummary
Monthly breakdown of Receipts and Payments
Updated Procedure with Bank Balances
Ultragrid Scrolling- Disable Scrolling in UltraWinGrid
efficient ways to insert multiple rows in SQL
Enable and Disable Constraints
Resetting Identity Column Value
Format function in sqlserver
introduction to CSS
Society act 1961
Conventions in Entity Framework Core
Bootstrap Get Started
What is HTML?
working with Sqlite database in xamarian
JavaScript Can Change HTML Content
what is an object
Difference between Xamarian and Xamarian.forms
Prerequisites for App Developement
Exploring Solution Explorer
Introduction to XAML
Basic Rules
What is Economic Moat?
What is Code-First?:
logo
To Place Your adv Contact

Ph - 9876543210