5

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, 300) -- Ricky 
INSERT INTO BASE_Invoice (InvoiceDate, CustomerId, Total) VALUES ('2022-01-08', 2, 400) -- Smith
INSERT INTO BASE_Invoice (InvoiceDate, CustomerId, Total) VALUES ('2022-01-09', 1, 600) -- Ricky

PaymntTable:

CREATE TABLE BASE_Payment
(
    PaymentId INT IDENTITY(1,1),
    PaymentDate DATE,
    CustomerId INT,
    Total MONEY,
    PRIMARY KEY(PaymentId)
)

INSERT INTO BASE_Payment (PaymentDate, CustomerId, Total) VALUES ('2022-01-03', 1, 400) -- Ricky
INSERT INTO BASE_Payment (PaymentDate, CustomerId, Total) VALUES ('2022-01-05', 1, 500) -- Ricky
INSERT INTO BASE_Payment (PaymentDate, CustomerId, Total) VALUES ('2022-01-05', 2, 300) -- Smith
INSERT INTO BASE_Payment (PaymentDate, CustomerId, Total) VALUES ('2022-01-07', 1, 600) -- Ricky
INSERT INTO BASE_Payment (PaymentDate, CustomerId, Total) VALUES ('2022-01-08', 2, 200) -- Smith

how can I retrieve statement view from above 3 tables records by customer wise (Full Statement and Date range)

NOTE

  1. when inserting a new customer with their opening balance then "Opening balance" should appear on 1st record

  2. if retrieve form Date Range selected customer, "previous records end balance" should come as a Opening Balance

How to retrieve Output should below screenshots,

Picture 01: Customer (Ricky) Full Statement

Note: when created this customer given opening balance

enter image description here

Picture 02: Customer (Ricky) Date Range Statement

Note: Date Range Opening balance should be "previous records end balance" not when created this customer given opening balance above output picture 01 2,500

enter image description here

Picture 03: Customer (Smith) Full Statement

Note: when created this customer Not given opening balance so no need to appear opening balance

enter image description here

Picture 04: Customer (Smith) Date Range Statement

Note: Date Range Opening balance should be previous records end balance 750 (above full statement 1st two records total)

enter image description here

I tried with below query, but I didn't get expected result like above 4 screenshots, main issue is opening balance when retrieving date range.

--YYYY-MM-DD (Date Format)

DECLARE @CustomerId INT  = 2;

SELECT 
[Date], 
Particulars, 
ABS(Debit) AS Debit, 
ABS(Credit) AS Credit, 
SUM(CASE WHEN Credit = 0 THEN Debit ELSE Credit END) OVER (ORDER BY [Date], [Particulars]) AS Balance 
FROM 
(
SELECT 
NULL AS [Date], 
'OPENING BALANCE' AS [Particulars], 
ISNULL(SalesOpeningBalance, 0) AS [Debit], 
0 AS [Credit], 
ISNULL(SalesOpeningBalance, 0) AS [Balance] 
FROM BASE_Customer 
WHERE (CustomerId = @CustomerId) 
UNION ALL 
SELECT 
BASE_Invoice.InvoiceDate AS [Date], 
'INVOICE' AS [Particulars], 
BASE_Invoice.Total AS [Debit], 
0 AS [Credit], 
ISNULL(BASE_Customer.SalesOpeningBalance, 0) AS [Balance] 
FROM BASE_Customer INNER JOIN BASE_Invoice ON BASE_Customer.CustomerId = BASE_Invoice.CustomerId 
WHERE (BASE_Customer.CustomerId = @CustomerId) --AND (BASE_Invoice.InvoiceDate BETWEEN '2022-01-04' AND '2022-01-05')
UNION ALL 
SELECT 
BASE_Payment.PaymentDate AS [Date], 
'PAYMENT' AS [Particulars], 
0 AS [Debit], 
-1.0 * BASE_Payment.Total AS [Credit], 
ISNULL(BASE_Customer.SalesOpeningBalance, 0) AS [Balance] 
FROM BASE_Customer INNER JOIN BASE_Payment ON BASE_Customer.CustomerId = BASE_Payment.CustomerId 
WHERE (BASE_Customer.CustomerId = @CustomerId) --AND (BASE_Payment.PaymentDate BETWEEN '2022-01-04' AND '2022-01-05')
) u 
ORDER BY [Date]
1
  • 4
    It would be useful to people trying to answer your question to see the query you used that didn’t return the correct results so they don’t go down the same path, I think. Otherwise you’ve done a great job of describing the problem and providing sample data. Thank you for that. Commented Feb 24, 2023 at 20:55

1 Answer 1

2

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 takes into account the comments regarding the 'opening balance' addition.

Amended demo data:

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); 

Amended query:

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

Amended result (for 2022-04-01 to 2022-05-01)

Date Particulars Debit Credit Balance
2022-03-01 OPENING BALANCE NULL NULL 1500.00
2022-04-01 INVOICE 200.00 NULL 1700.00
2022-05-01 INVOICE 100.00 NULL 1600.00
2022-05-01 PAYMENT NULL 500.00 2100.00

Amended result (for 2022-05-01 to 2022-07-01) (Picture 02)

Date Particulars Debit Credit Balance
2022-04-01 OPENING BALANCE NULL NULL 1700.00
2022-05-01 INVOICE 100.00 NULL 1600.00
2022-05-01 PAYMENT NULL 500.00 2100.00
2022-06-01 INVOICE 200.00 NULL 1900.00
2022-07-01 INVOICE 500.00 NULL 1400.00
2022-07-01 PAYMENT NULL 600.00 2000.00
8
  • 3
    What is the purpose of SELECT TOP 100 PERCENT, why not just SELECT? Commented Feb 25, 2023 at 19:15
  • output is 100% Ok but only one issue is if date range select opening balance (before date range starting Balance) not mentioning in first row. Picture 02 and Picture 04 in the question
    – HRSO1
    Commented Feb 26, 2023 at 18:39
  • 1
    confusion in SELECT TOP 100 PERCENT? @PatrickHurst what is the purpose of use? anther main Problem is OPENING BALANCE not getting when date from to date given.. otherwise confusion of first row balance. (it show over value).
    – M.I.M.F
    Commented Feb 26, 2023 at 18:42
  • 1
    SELECT TOP 100 PERCENT Allows the use of an order by within a CTE (or other statements where order by is not normally allowed) without excluding rows. Commented Feb 26, 2023 at 20:07
  • 1
    @HRSO1 please see the amended stuff added to the bottom of the answer. Commented Feb 27, 2023 at 17:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.