GetMonthlyCashFlowSummary

Strategy

1. Get the opening balance from AccountHierarchyNew (as before).
2. Aggregate monthly Receipts and Payments from VoucherDetails where Receipt_PaymentMode = 'Cash'.
3. Calculate Net Flow = Receipts - Payments.
4. Use SUM(...) OVER (ORDER BY Month) to compute the running balance.
5. Prepend the opening balance as the first row.

✅ 

Final Monthly Cash Flow Procedure

ALTER PROCEDURE [dbo].[GetMonthlyCashFlowSummary] @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()); -- 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: Monthly Receipts and Payments from VoucherDetails ;WITH MonthlyData AS ( SELECT FORMAT(ReceiptDate, 'yyyy-MM') AS Month, SUM(CASE WHEN TransactionType = 'Credit' THEN TransactionNetAmount ELSE 0 END) AS Receipts, SUM(CASE WHEN TransactionType = 'Debit' THEN TransactionNetAmount ELSE 0 END) AS Payments FROM VoucherDetails WHERE Receipt_PaymentMode = 'Cash' AND ReceiptDate >= @FromDate AND ReceiptDate <= @ToDate GROUP BY FORMAT(ReceiptDate, 'yyyy-MM') ), WithNetFlow AS ( SELECT Month, Receipts, Payments, (Receipts - Payments) AS NetFlow FROM MonthlyData ), WithRunningBalance AS ( SELECT Month, Receipts, Payments, NetFlow, SUM(NetFlow) OVER (ORDER BY Month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + ISNULL(@OpeningBalance, 0) AS RunningBalance FROM WithNetFlow ) -- Step 3: Final output with opening balance row SELECT 'Opening Balance' AS Month, NULL AS Receipts, NULL AS Payments, NULL AS NetFlow, @OpeningBalance AS RunningBalance UNION ALL SELECT Month, Receipts, Payments, NetFlow, RunningBalance FROM WithRunningBalance ORDER BY CASE WHEN Month = 'Opening Balance' THEN 0 ELSE 1 END, Month; END;



🧾 Output Example

Would you like to integrate this into your main summary procedure or keep it as a separate monthly report? I can also help visualize this in a WinForms grid or chart if you’d like.