Format function in sqlserver

FORMAT FUNCTION

Some times it so happens that we have  many leading zeros after a decimal number .

For example I am getting a result of a query say value of closing stock  as 66000.000000 .

However I need to keep only 2 zeros after the decimal point and want the result as 66000.00 .

Here is a common trick of using the Format function.

Enable and Disable Constraints

1. Primary key constraint
2. Default key Constraint
3. Check Constraint
4. Foreign key Constraint
5. Unique key Constraint

Sometimes we need to disable constraints to work with...Here is how to do it

Resetting Identity Column Value

Identity column of a table is a column whose value increases automatically. A user generally cannot insert a value into an identity column. A table can have only one column that is defined with the identity attribute.

Syntax :

IDENTITY [ ( seed , increment ) ]

Default value of identity is IDENTITY (1,1).

Seed : The seed represents the starting value of an ID and the default value of seed is 1.

Increment : It will represent the incremental value of the ID and the default value of increment is 1.

efficient ways to insert multiple rows in SQL

Inserting multiple rows efficiently can depend on your SQL dialect, the volume of data, and whether you're scripting or working in an application context. Here are some broadly effective methods across different scenarios:

Getting Cash-flow-statement

• 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.

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.

✅ 

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
  ...	...	...	...	...

   

   

Updated Procedure with Bank Balances