Monthly breakdown of Receipts and Payments

• • Monthly breakdown of Receipts and Payments
  • 💰 Running cash balance per month
  • 🧾 Opening balance carried forward from April 1st

  This will give you a ledger-style view of cash flow across months, which is perfect for financial tracking.

   

  ✅ What We'll Build

   

  Month	Receipts	Payments	Net Flow	Running Balance
  Apr 2025	15000.00	8000.00	7000.00	48000.00
  May 2025	12000.00	5000.00	7000.00	55000.00
  ...	...	...	...	...

   

  🧠 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

   

  Month	Receipts	Payments	NetFlow	RunningBalance
  Opening Balance	NULL	NULL	NULL	41000.00
  2025-04	15000.00	8000.00	7000.00	48000.00
  2025-05	12000.00	5000.00	7000.00	55000.00
  ...	...	...	...	...