snippetsqlMinor

How to Retrieve (Debit, Credit and Balance) from daily Transaction tables (Customer, Invoice, Payment) Full and Date Range)

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

tablesfullpaymentcreditbalancedebitrangedateretrievetransaction

Problem

There are 3 Tables Customer, Invoice and Payment. Customer Link with Invoice and Payment table. Question is, need to retrieve statement wise Debit, Credit and Balance. main issue is opening balance not getting from date range. I provide sample data with queries.

Customer Table:

(When inserting a new customer, if they have pending balance assigning with opening balance otherwise keep blank (null))

CREATE TABLE BASE_Customer
(
    CustomerId INT IDENTITY(1,1),
    CustomerName VARCHAR(45),
    SalesOpeningBalance MONEY NULL,
    PRIMARY KEY(CustomerId)
)

INSERT INTO BASE_Customer (CustomerName, SalesOpeningBalance) VAlUES ('Ricky', 2500) -- with opening balance.
INSERT INTO BASE_Customer (CustomerName) VAlUES ('Smith') -- without opening balance.

Invoice Table:

```
CREATE TABLE BASE_Invoice
(
InvoiceId INT IDENTITY(1,1),
InvoiceDate DATE,
CustomerId INT,
Total MONEY,
PRIMARY KEY(InvoiceId)
)

INSERT INTO BASE_Invoice (InvoiceDate, CustomerId, Total) VALUES ('2022-01-01', 1, 500) -- Ricky
INSERT INTO BASE_Invoice (InvoiceDate, CustomerId, Total) VALUES ('2022-01-02', 2, 250) -- Smith
INSERT INTO BASE_Invoice (InvoiceDate, CustomerId, Total) VALUES ('2022-01-02', 1, 100) -- Ricky
INSERT INTO BASE_Invoice (InvoiceDate, CustomerId, Total) VALUES ('2022-01-03', 1, 400) -- Ricky
INSERT INTO BASE_Invoice (InvoiceDate, CustomerId, Total) VALUES ('2022-01-03', 2, 500) -- Smith
INSERT INTO BASE_Invoice (InvoiceDate, CustomerId, Total) VALUES ('2022-01-04', 1, 200) -- Ricky
INSERT INTO BASE_Invoice (InvoiceDate, CustomerId, Total) VALUES ('2022-01-04', 2, 800) -- Smith
INSERT INTO BASE_Invoice (InvoiceDate, CustomerId, Total) VALUES ('2022-01-05', 1, 100) -- Ricky
INSERT INTO BASE_Invoice (InvoiceDate, CustomerId, Total) VALUES ('2022-01-06', 1, 200) -- Ricky
INSERT INTO BASE_Invoice (InvoiceDate, CustomerId, Total) VALUES ('2022-01-07', 1, 500) -- Ricky
INSERT INTO BASE_Invoice (InvoiceDate, CustomerId, Total) VALUES ('2022-01-08', 1, 30

Solution

I compressed your example DDL/DML a bit:

DECLARE @BASE_Customer TABLE (CustomerId INT IDENTITY(1,1) PRIMARY KEY, CustomerName VARCHAR(45), SalesOpeningBalance DECIMAL(10,2) NULL);
INSERT INTO @BASE_Customer (CustomerName, SalesOpeningBalance) VAlUES 
('Ricky', 2500), ('Smith', NULL);

DECLARE @BASE_Invoice TABLE (InvoiceId INT IDENTITY(1,1) PRIMARY KEY, InvoiceDate DATE, CustomerId INT, Total DECIMAL(10,2));
INSERT INTO @BASE_Invoice (InvoiceDate, CustomerId, Total) VALUES 
('2022-01-01', 1, 500), ('2022-01-02', 2, 250), ('2022-01-02', 1, 100), ('2022-01-03', 1, 400),  
('2022-01-03', 2, 500), ('2022-01-04', 1, 200), ('2022-01-04', 2, 800), ('2022-01-05', 1, 100),  
('2022-01-06', 1, 200), ('2022-01-07', 1, 500), ('2022-01-08', 1, 300), ('2022-01-08', 2, 400), 
('2022-01-09', 1, 600); 

DECLARE @BASE_Payment TABLE (PaymentId INT IDENTITY(1,1) PRIMARY KEY, PaymentDate DATE, CustomerId INT, Total DECIMAL(10,2));
INSERT INTO @BASE_Payment (PaymentDate, CustomerId, Total) VALUES 
('2022-01-03', 1, 400), ('2020-01-05', 1, 500), ('2020-01-05', 2, 300), ('2020-01-07', 1, 600), 
('2020-01-08', 2, 200);

DECLARE @CustomerID INT, @DateFrom DATE, @DateTo DATE

SET @CustomerID = 1
SET @DateFrom = '2022-01-03'
SET @DateTo = '2022-01-08'

SELECT a.Date, a.Particulars, a.Debit, a.Credit, a.Balance
  FROM (
SELECT CustomerID, Date, Particulars, Debit, Credit, SUM(Balance) OVER (PARTITION BY CustomerID ORDER BY Date, a.Particulars) AS Balance
  FROM (
        SELECT CAST(NULL AS DATE) AS Date, 'OPENING BALANCE' AS Particulars, CAST(NULL AS DECIMAL(10,2)) AS Debit, CAST(NULL AS DECIMAL(10,2)) AS Credit, SalesOpeningBalance AS Balance, CustomerID
          FROM @BASE_Customer
         WHERE SalesOpeningBalance IS NOT NULL
        UNION ALL
        SELECT TOP 100 PERCENT a.Date, a.Particulars, a.Debit, a.Credit, Balance, CustomerID
          FROM (
                SELECT InvoiceDate AS Date, 'INVOICE' AS Particulars, Total AS Debit, CAST(NULL AS DECIMAL(10,2)) AS Credit, -1*Total AS Balance, CustomerID
                  FROM @BASE_Invoice 
                UNION ALL
                SELECT PaymentDate AS Date, 'PAYMENT' AS Particulars,  CAST(NULL AS DECIMAL(10,2)) AS Debit, Total AS Credit, Total AS Balance, CustomerID
                  FROM @BASE_Payment
               ) a
        ORDER BY CustomerID, Date
       ) a
       ) a
 WHERE (
            @CustomerID = a.CustomerId
         OR @CustomerID IS NULL
       )
   AND (
            Date BETWEEN @DateFrom AND @DateTo
         OR @DateTo IS NULL
         OR @DateFrom IS NULL
       )

This is one approach that gets you the required output. First, we UNION the three sets of data together in a unified format. Notice that I put the debits and credits in the balance column, and multiplied the debits by negative 1,
Once we have a unified set of data we can use a windowed sum function to figure out the running balance.

I've applied the parameters after fetching all of the data, as we still need anything pre-dating the requested start date to know where to start the running balance. I also had to make a decision on how to break ties, since we're only use DATE, so I ordered INVOICES first.

Using @CustomerID = 1, @DateFrom = 2022-01-03 and @DateTo = 2022-01-08:

Date
Particulars
Debit
Credit
Balance

2022-01-03
INVOICE
400.00
NULL
2600.00

2022-01-03
PAYMENT
NULL
400.00
3000.00

2022-01-04
INVOICE
200.00
NULL
2800.00

2022-01-05
INVOICE
100.00
NULL
2700.00

2022-01-06
INVOICE
200.00
NULL
2500.00

2022-01-07
INVOICE
500.00
NULL
2000.00

2022-01-08
INVOICE
300.00
NULL
1700.00

Using @CustomerID = 1 and NULL dates:

Date
Particulars
Debit
Credit
Balance

NULL
OPENING BALANCE
NULL
NULL
2500.00

2020-01-05
PAYMENT
NULL
500.00
3000.00

2020-01-07
PAYMENT
NULL
600.00
3600.00

2022-01-01
INVOICE
500.00
NULL
3100.00

2022-01-02
INVOICE
100.00
NULL
3000.00

2022-01-03
INVOICE
400.00
NULL
2600.00

2022-01-03
PAYMENT
NULL
400.00
3000.00

2022-01-04
INVOICE
200.00
NULL
2800.00

2022-01-05
INVOICE
100.00
NULL
2700.00

2022-01-06
INVOICE
200.00
NULL
2500.00

2022-01-07
INVOICE
500.00
NULL
2000.00

2022-01-08
INVOICE
300.00
NULL
1700.00

2022-01-09
INVOICE
600.00
NULL
1100.00

Things to note:
You should probably use a DATETIME column instead of a date, so the chances of two transactions tying are greatly reduced. You can reduce this further if DATETIME2 is available. It's a good idea to store DATETIME in UTC to prevent timezone confusion, and to label the column as such (TransactionDateTimeUTC for example).
MONEY is a bad choice for a currency column as there are underlying issues with it when it comes to rounding.
You can insert up to 1000 rows in a single INSERT .. VALUES statement.
I used table variables, as they don't require any cleanup, but that's just a personal preference.

Edit:

As it turns out the data provided used a date format of YYYY-DD-MM, not the ISO standard of YYYY-MM-DD. This edit provides example data using different months, rather than days. This edit also tak

Code Snippets

DECLARE @BASE_Customer TABLE (CustomerId INT IDENTITY(1,1) PRIMARY KEY, CustomerName VARCHAR(45), SalesOpeningBalance DECIMAL(10,2) NULL);
INSERT INTO @BASE_Customer (CustomerName, SalesOpeningBalance) VAlUES 
('Ricky', 2500), ('Smith', NULL);

DECLARE @BASE_Invoice TABLE (InvoiceId INT IDENTITY(1,1) PRIMARY KEY, InvoiceDate DATE, CustomerId INT, Total DECIMAL(10,2));
INSERT INTO @BASE_Invoice (InvoiceDate, CustomerId, Total) VALUES 
('2022-01-01', 1, 500), ('2022-01-02', 2, 250), ('2022-01-02', 1, 100), ('2022-01-03', 1, 400),  
('2022-01-03', 2, 500), ('2022-01-04', 1, 200), ('2022-01-04', 2, 800), ('2022-01-05', 1, 100),  
('2022-01-06', 1, 200), ('2022-01-07', 1, 500), ('2022-01-08', 1, 300), ('2022-01-08', 2, 400), 
('2022-01-09', 1, 600); 

DECLARE @BASE_Payment TABLE (PaymentId INT IDENTITY(1,1) PRIMARY KEY, PaymentDate DATE, CustomerId INT, Total DECIMAL(10,2));
INSERT INTO @BASE_Payment (PaymentDate, CustomerId, Total) VALUES 
('2022-01-03', 1, 400), ('2020-01-05', 1, 500), ('2020-01-05', 2, 300), ('2020-01-07', 1, 600), 
('2020-01-08', 2, 200);

DECLARE @CustomerID INT, @DateFrom DATE, @DateTo DATE

SET @CustomerID = 1
SET @DateFrom = '2022-01-03'
SET @DateTo = '2022-01-08'

SELECT a.Date, a.Particulars, a.Debit, a.Credit, a.Balance
  FROM (
SELECT CustomerID, Date, Particulars, Debit, Credit, SUM(Balance) OVER (PARTITION BY CustomerID ORDER BY Date, a.Particulars) AS Balance
  FROM (
        SELECT CAST(NULL AS DATE) AS Date, 'OPENING BALANCE' AS Particulars, CAST(NULL AS DECIMAL(10,2)) AS Debit, CAST(NULL AS DECIMAL(10,2)) AS Credit, SalesOpeningBalance AS Balance, CustomerID
          FROM @BASE_Customer
         WHERE SalesOpeningBalance IS NOT NULL
        UNION ALL
        SELECT TOP 100 PERCENT a.Date, a.Particulars, a.Debit, a.Credit, Balance, CustomerID
          FROM (
                SELECT InvoiceDate AS Date, 'INVOICE' AS Particulars, Total AS Debit, CAST(NULL AS DECIMAL(10,2)) AS Credit, -1*Total AS Balance, CustomerID
                  FROM @BASE_Invoice 
                UNION ALL
                SELECT PaymentDate AS Date, 'PAYMENT' AS Particulars,  CAST(NULL AS DECIMAL(10,2)) AS Debit, Total AS Credit, Total AS Balance, CustomerID
                  FROM @BASE_Payment
               ) a
        ORDER BY CustomerID, Date
       ) a
       ) a
 WHERE (
            @CustomerID = a.CustomerId
         OR @CustomerID IS NULL
       )
   AND (
            Date BETWEEN @DateFrom AND @DateTo
         OR @DateTo IS NULL
         OR @DateFrom IS NULL
       )

DECLARE @BASE_Customer TABLE (CustomerId INT IDENTITY(1,1) PRIMARY KEY, CustomerName VARCHAR(45), SalesOpeningBalance DECIMAL(10,2) NULL);
INSERT INTO @BASE_Customer (CustomerName, SalesOpeningBalance) VAlUES 
('Ricky', 2500), ('Smith', NULL);

DECLARE @BASE_Invoice TABLE (InvoiceId INT IDENTITY(1,1) PRIMARY KEY, InvoiceDate DATE, CustomerId INT, Total DECIMAL(10,2));
INSERT INTO @BASE_Invoice (InvoiceDate, CustomerId, Total) VALUES 
('2022-01-01', 1, 500), ('2022-02-01', 2, 250), ('2022-02-01', 1, 100), ('2022-03-01', 1, 400),  
('2022-03-01', 2, 500), ('2022-04-01', 1, 200), ('2022-04-01', 2, 800), ('2022-05-01', 1, 100),  
('2022-06-01', 1, 200), ('2022-07-01', 1, 500), ('2022-08-01', 1, 300), ('2022-08-01', 2, 400), 
('2022-09-01', 1, 600); 

DECLARE @BASE_Payment TABLE (PaymentId INT IDENTITY(1,1) PRIMARY KEY, PaymentDate DATE, CustomerId INT, Total DECIMAL(10,2));
INSERT INTO @BASE_Payment (PaymentDate, CustomerId, Total) VALUES 
('2022-03-01', 1, 400), ('2022-05-01', 1, 500), ('2022-05-01', 2, 300), ('2022-07-01', 1, 600), 
('2022-08-01', 2, 200);

DECLARE @CustomerID INT, @DateFrom DATE, @DateTo DATE

SET @CustomerID = 1
SET @DateFrom = '2022-04-01'
SET @DateTo = '2022-05-01'

;WITH allItems AS (
        SELECT CustomerID, Date, Particulars, Debit, Credit, SUM(Balance) OVER (PARTITION BY CustomerID ORDER BY Date, a.Particulars) AS Balance
          FROM (
                SELECT CAST(NULL AS DATE) AS Date, 'OPENING BALANCE' AS Particulars, CAST(NULL AS DECIMAL(10,2)) AS Debit, CAST(NULL AS DECIMAL(10,2)) AS Credit, SalesOpeningBalance AS Balance, CustomerID
                  FROM @BASE_Customer
                 WHERE SalesOpeningBalance IS NOT NULL
                UNION ALL
                SELECT a.Date, a.Particulars, a.Debit, a.Credit, Balance, CustomerID
                  FROM (
                        SELECT InvoiceDate AS Date, 'INVOICE' AS Particulars, Total AS Debit, CAST(NULL AS DECIMAL(10,2)) AS Credit, -1*Total AS Balance, CustomerID
                          FROM @BASE_Invoice 
                        UNION ALL
                        SELECT PaymentDate AS Date, 'PAYMENT' AS Particulars,  CAST(NULL AS DECIMAL(10,2)) AS Debit, Total AS Credit, Total AS Balance, CustomerID
                          FROM @BASE_Payment
                       ) a
               ) a
)

SELECT a.Date, a.Particulars, a.Debit, a.Credit, a.Balance
  FROM (
        SELECT TOP 1 CustomerID, Date, 'OPENING BALANCE' AS Particulars, NULL AS Debit, NULL AS Credit, Balance 
          FROM allItems a
         WHERE Date < @DateFrom
         ORDER BY Date DESC
        UNION ALL
        SELECT CustomerID, Date, Particulars, Debit, Credit, Balance
          FROM allItems a
         WHERE (
                   @CustomerID = a.CustomerId
                OR @CustomerID IS NULL
               )
           AND (
                    Date BETWEEN @DateFrom AND @DateTo
                 OR @DateTo IS NULL
                 OR @DateFrom IS NULL
               )
       ) a

Context

StackExchange Database Administrators Q#324022, answer score: 2

Revisions (0)

No revisions yet.