Updated Procedure with Bank Balances

SqlServer, SqlServer Useful functions

Introduction


ALTER PROCEDURE [dbo].[GetCategoryAccountSummary]
    @FromDate DATE = NULL,
    @ToDate DATE = NULL
AS
BEGIN
    -- Normalize date range
    IF @FromDate IS NULL SET @FromDate = DATEFROMPARTS(YEAR(GETDATE()), 4, 1);
    IF @ToDate IS NULL SET @ToDate = EOMONTH(GETDATE());
 
    -- Cash Opening Balance
    DECLARE @OpeningCashBalance DECIMAL(18,2);
    SELECT @OpeningCashBalance = SUM(TransactionNetAmount)
    FROM AccountHierarchyNew
    WHERE Category = 'Cash-In-Hand'
      AND TransactionDate = DATEFROMPARTS(YEAR(@FromDate), 4, 1);
 
    -- 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 ReceiptDate BETWEEN @FromDate AND @ToDate;
 
    DECLARE @CashBalance DECIMAL(18,2) = ISNULL(@OpeningCashBalance, 0) + ISNULL(@CashMovement, 0);
 
    -- Bank Opening Balance
    DECLARE @OpeningBankBalance DECIMAL(18,2);
    SELECT @OpeningBankBalance = SUM(TransactionNetAmount)
    FROM AccountHierarchyNew
    WHERE AccountType IN ('Canara Bank Current A/c', 'ICICI Bank Current A/c')
      AND TransactionDate = DATEFROMPARTS(YEAR(@FromDate), 4, 1);
 
    -- Bank Movement
    DECLARE @BankMovement DECIMAL(18,2);
    SELECT @BankMovement = 
        SUM(CASE WHEN TransactionType = 'Credit' THEN TransactionNetAmount ELSE 0 END) -
        SUM(CASE WHEN TransactionType = 'Debit' THEN TransactionNetAmount ELSE 0 END)
    FROM VoucherDetails
    WHERE AccountType IN ('Canara Bank Current A/c', 'ICICI Bank Current A/c')
      AND ReceiptDate BETWEEN @FromDate AND @ToDate;
 
    DECLARE @BankBalance DECIMAL(18,2) = ISNULL(@OpeningBankBalance, 0) + ISNULL(@BankMovement, 0);
 
    -- Base data
    CREATE TABLE #BaseData (
        Section NVARCHAR(20),
        Category NVARCHAR(100),
        Account NVARCHAR(100),
        Amount DECIMAL(18, 2),
        SortRank INT
    );
 
    -- Actual voucher data
    INSERT INTO #BaseData (Section, Category, Account, Amount, SortRank)
    SELECT  
        CASE 
            WHEN Prefix IN ('R', 'A') THEN 'Receipts'
            WHEN Prefix = 'V' THEN 'Payments'
        END,
        Receipt_PaymentMode,
        AccountType,
        TransactionNetAmount,
        2
    FROM VoucherDetails
    WHERE Prefix IN ('R', 'A', 'V')
      AND ReceiptDate BETWEEN @FromDate AND @ToDate;
 
    -- Synthetic Opening Balances
    INSERT INTO #BaseData VALUES ('Receipts', 'Cash', 'Opening Balance', @CashBalance, 1);
    INSERT INTO #BaseData VALUES ('Receipts', 'Bank', 'Opening Balance', @BankBalance, 1);
 
    -- Synthetic Closing Balances
    INSERT INTO #BaseData VALUES ('Payments', 'Cash', 'Closing Balance', @CashBalance, 3);
    INSERT INTO #BaseData VALUES ('Payments', 'Bank', 'Closing Balance', @BankBalance, 3);
 
    -- CTEs
    WITH ReceiptsCTE AS (
        SELECT 
            ROW_NUMBER() OVER (ORDER BY SortRank, Category, Account) AS RowNum,
            Category AS ReceiptCategory,
            Account AS ReceiptAccount,
            SUM(Amount) AS Receipts
        FROM #BaseData
        WHERE Section = 'Receipts'
        GROUP BY Category, Account, SortRank
    ),
    PaymentsCTE AS (
        SELECT 
            ROW_NUMBER() OVER (ORDER BY SortRank, Category, Account) AS RowNum,
            Category AS PaymentCategory,
            Account AS PaymentAccount,
            SUM(Amount) AS Payments
        FROM #BaseData
        WHERE Section = 'Payments'
        GROUP BY Category, Account, SortRank
    ),
    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
    )
 
    -- Final output
    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
        FROM Combined
    ) AS FinalResult
    ORDER BY SortOrder,
             CASE 
                 WHEN ReceiptCategory = 'Cash' AND ReceiptAccount = 'Opening Balance' THEN 0
                 WHEN ReceiptCategory = 'Bank' AND ReceiptAccount = 'Opening Balance' THEN 1
                 ELSE 2
             END,
             CASE 
                 WHEN PaymentCategory = 'Cash' AND PaymentAccount = 'Closing Balance' THEN 2
                 WHEN PaymentCategory = 'Bank' AND PaymentAccount = 'Closing Balance' THEN 3
                 ELSE 1
             END,
             ReceiptCategory,
             ReceiptAccount;
 
    DROP TABLE #BaseData;
END;

Description


ALTER PROCEDURE [dbo].[GetCategoryAccountSummary]
    @FromDate DATE = NULL,
    @ToDate DATE = NULL
AS
BEGIN
    -- Normalize date range
    IF @FromDate IS NULL SET @FromDate = DATEFROMPARTS(YEAR(GETDATE()), 4, 1);
    IF @ToDate IS NULL SET @ToDate = EOMONTH(GETDATE());
 
    -- Cash Opening Balance
    DECLARE @OpeningCashBalance DECIMAL(18,2);
    SELECT @OpeningCashBalance = SUM(TransactionNetAmount)
    FROM AccountHierarchyNew
    WHERE Category = 'Cash-In-Hand'
      AND TransactionDate = DATEFROMPARTS(YEAR(@FromDate), 4, 1);
 
    -- 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 ReceiptDate BETWEEN @FromDate AND @ToDate;
 
    DECLARE @CashBalance DECIMAL(18,2) = ISNULL(@OpeningCashBalance, 0) + ISNULL(@CashMovement, 0);
 
    -- Bank Opening Balance
    DECLARE @OpeningBankBalance DECIMAL(18,2);
    SELECT @OpeningBankBalance = SUM(TransactionNetAmount)
    FROM AccountHierarchyNew
    WHERE AccountType IN ('Canara Bank Current A/c', 'ICICI Bank Current A/c')
      AND TransactionDate = DATEFROMPARTS(YEAR(@FromDate), 4, 1);
 
    -- Bank Movement
    DECLARE @BankMovement DECIMAL(18,2);
    SELECT @BankMovement = 
        SUM(CASE WHEN TransactionType = 'Credit' THEN TransactionNetAmount ELSE 0 END) -
        SUM(CASE WHEN TransactionType = 'Debit' THEN TransactionNetAmount ELSE 0 END)
    FROM VoucherDetails
    WHERE AccountType IN ('Canara Bank Current A/c', 'ICICI Bank Current A/c')
      AND ReceiptDate BETWEEN @FromDate AND @ToDate;
 
    DECLARE @BankBalance DECIMAL(18,2) = ISNULL(@OpeningBankBalance, 0) + ISNULL(@BankMovement, 0);
 
    -- Base data
    CREATE TABLE #BaseData (
        Section NVARCHAR(20),
        Category NVARCHAR(100),
        Account NVARCHAR(100),
        Amount DECIMAL(18, 2),
        SortRank INT
    );
 
    -- Actual voucher data
    INSERT INTO #BaseData (Section, Category, Account, Amount, SortRank)
    SELECT  
        CASE 
            WHEN Prefix IN ('R', 'A') THEN 'Receipts'
            WHEN Prefix = 'V' THEN 'Payments'
        END,
        Receipt_PaymentMode,
        AccountType,
        TransactionNetAmount,
        2
    FROM VoucherDetails
    WHERE Prefix IN ('R', 'A', 'V')
      AND ReceiptDate BETWEEN @FromDate AND @ToDate;
 
    -- Synthetic Opening Balances
    INSERT INTO #BaseData VALUES ('Receipts', 'Cash', 'Opening Balance', @CashBalance, 1);
    INSERT INTO #BaseData VALUES ('Receipts', 'Bank', 'Opening Balance', @BankBalance, 1);
 
    -- Synthetic Closing Balances
    INSERT INTO #BaseData VALUES ('Payments', 'Cash', 'Closing Balance', @CashBalance, 3);
    INSERT INTO #BaseData VALUES ('Payments', 'Bank', 'Closing Balance', @BankBalance, 3);
 
    -- CTEs
    WITH ReceiptsCTE AS (
        SELECT 
            ROW_NUMBER() OVER (ORDER BY SortRank, Category, Account) AS RowNum,
            Category AS ReceiptCategory,
            Account AS ReceiptAccount,
            SUM(Amount) AS Receipts
        FROM #BaseData
        WHERE Section = 'Receipts'
        GROUP BY Category, Account, SortRank
    ),
    PaymentsCTE AS (
        SELECT 
            ROW_NUMBER() OVER (ORDER BY SortRank, Category, Account) AS RowNum,
            Category AS PaymentCategory,
            Account AS PaymentAccount,
            SUM(Amount) AS Payments
        FROM #BaseData
        WHERE Section = 'Payments'
        GROUP BY Category, Account, SortRank
    ),
    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
    )
 
    -- Final output
    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
        FROM Combined
    ) AS FinalResult
    ORDER BY SortOrder,
             CASE 
                 WHEN ReceiptCategory = 'Cash' AND ReceiptAccount = 'Opening Balance' THEN 0
                 WHEN ReceiptCategory = 'Bank' AND ReceiptAccount = 'Opening Balance' THEN 1
                 ELSE 2
             END,
             CASE 
                 WHEN PaymentCategory = 'Cash' AND PaymentAccount = 'Closing Balance' THEN 2
                 WHEN PaymentCategory = 'Bank' AND PaymentAccount = 'Closing Balance' THEN 3
                 ELSE 1
             END,
             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