1

I have more than 1 table such as INVOICE, PAYMENT, RETURN_INVOICE, RETURN_PAYMENT, etc. i want to retrieve data CREDIT, DEBIT AND BALANCE each customer wise

below are tables and insert query statements,

CREATE TABLE Customer
(
    CustomerId INT IDENTITY(1,1),
    RegistrationDate DATE,
    CustomerName VARCHAR(45),
    OpeningBalance MONEY,
    PRIMARY KEY(CustomerId)
)
INSERT INTO Customer (CustomerName, RegistrationDate, OpeningBalance) VAlUES ('JOHN', '2020-01-15', 1000)


CREATE TABLE Invoice
(
    InvoiceId INT IDENTITY(1,1),
    InvoiceDate DATE,
    CustomerId INT,
    InvoiceTotal MONEY,
    PRIMARY KEY(InvoiceId)
)
INSERT INTO Invoice (InvoiceDate, CustomerId, InvoiceTotal) VAlUES ('2020-01-16', 1, 2000)
INSERT INTO Invoice (InvoiceDate, CustomerId, InvoiceTotal) VAlUES ('2020-01-17', 1, 500)
INSERT INTO Invoice (InvoiceDate, CustomerId, InvoiceTotal) VAlUES ('2020-01-17', 1, 250)
INSERT INTO Invoice (InvoiceDate, CustomerId, InvoiceTotal) VAlUES ('2020-01-20', 1, 1000)
INSERT INTO Invoice (InvoiceDate, CustomerId, InvoiceTotal) VAlUES ('2020-01-22', 1, 2250)
INSERT INTO Invoice (InvoiceDate, CustomerId, InvoiceTotal) VAlUES ('2020-01-24', 1, 1750)
INSERT INTO Invoice (InvoiceDate, CustomerId, InvoiceTotal) VAlUES ('2020-01-28', 1, 3000)


CREATE TABLE Payment
(
    PaymentId INT IDENTITY(1,1),
    PaymentDate DATE,
    CustomerId INT,
    PaymentTotal MONEY,
    PRIMARY KEY(PaymentId)
)
INSERT INTO Payment (PaymentDate, CustomerId, PaymentTotal) VAlUES ('2020-01-18', 1, 750)
INSERT INTO Payment (PaymentDate, CustomerId, PaymentTotal) VAlUES ('2020-01-20', 1, 2000)
INSERT INTO Payment (PaymentDate, CustomerId, PaymentTotal) VAlUES ('2020-01-23', 1, 5000)
INSERT INTO Payment (PaymentDate, CustomerId, PaymentTotal) VAlUES ('2020-01-26', 1, 200)
INSERT INTO Payment (PaymentDate, CustomerId, PaymentTotal) VAlUES ('2020-01-28', 1, 500)


CREATE TABLE ReturnInvoice
(
    ReturnInvoiceId INT IDENTITY(1,1),
    ReturnInvoiceDate DATE,
    CustomerId INT,
    ReturnInvoiceTotal MONEY,
    PRIMARY KEY(ReturnInvoiceId)
)
INSERT INTO ReturnInvoice (ReturnInvoiceDate, CustomerId, ReturnInvoiceTotal) VAlUES ('2020-01-25', 1, 500)
INSERT INTO ReturnInvoice (ReturnInvoiceDate, CustomerId, ReturnInvoiceTotal) VAlUES ('2020-01-28', 1, 300)
INSERT INTO ReturnInvoice (ReturnInvoiceDate, CustomerId, ReturnInvoiceTotal) VAlUES ('2020-01-29', 1, 1000)


CREATE TABLE ReturnPayment
(
    ReturnPaymentId INT IDENTITY(1,1),
    ReturnPaymentDate DATE,
    CustomerId INT,
    ReturnPaymentTotal MONEY,
    PRIMARY KEY(ReturnPaymentId)
)
INSERT INTO ReturnPayment (ReturnPaymentDate, CustomerId, ReturnPaymentTotal) VAlUES ('2020-01-21', 1, 500)
INSERT INTO ReturnPayment (ReturnPaymentDate, CustomerId, ReturnPaymentTotal) VAlUES ('2020-01-27', 1, 2000)

I want to retrieve the output like this,

enter image description here

  • All the table should be inner join to customer (CustomerId Is Foreign Key)
  • Need to retrieve extra column Description and mention the table transaction name and No. (3rd column in the picture above)
  • Customer OPENING BALANCE should come on top Row that is from Customer Table
  • Ordered by date and its no under it.

How to get the Expected output on above picture.

1 Answer 1

1

I believe this will do what you need. I appreciate you making it easy to write by providing a solid MVCE.

We have 5 queries in UNION ALL to generate our data set, and one outer query to generate the running total. The reason we have 5 queries in the union is that we are using one for the Opening Balance, and the other 4 for each transaction type.

I cheated a bit with the running calculation using ORDER BY Date, Description in the balance. Because we generated the number inside Description. There is surely a cleaner way to do that. If you need to order by a specific type you could add an additional column to the union queries and code them (1, 2, 3, 4, 5) and then order by that column in the outer query.

However, this query does get you to the goal line:

SELECT
  CustomerName,
  [Date],
  Description,
  ABS(Debit) as Debit,
  ABS(Credit) as Credit,
  SUM(CASE WHEN Credit = 0 THEN DEBIT ELSE Credit END) OVER (PARTITION BY CustomerId ORDER BY [Date], Description)  as Balance
FROM

(
    SELECT
      CustomerId,
      CustomerName,
      RegistrationDate as [Date],
      'OPENING BALANCE' as Description,
      OpeningBalance as Debit,
      0 as Credit,
      OpeningBalance as Balance
    FROM Customer

UNION ALL

    SELECT
      c.CustomerId,
      CustomerName, 
      InvoiceDate as [Date], 
      'Invoice No. ' + CAST((ROW_NUMBER() OVER (PARTITION BY c.CustomerId ORDER BY c.RegistrationDate, InvoiceDate)) AS VARCHAR(10)) AS Description,
      InvoiceTotal as Debit, 
      0 as Credit, 
      OpeningBalance
    FROM Customer c
    JOIN Invoice i ON c.CustomerId = i.CustomerId

UNION ALL

    SELECT
      c.CustomerId,
      CustomerName, 
      PaymentDate,
      'Payment No. ' + CAST((ROW_NUMBER() OVER (PARTITION BY c.CustomerId ORDER BY c.RegistrationDate, PaymentDate)) AS VARCHAR(10)) AS Description,
      0, 
      -1.0 * PaymentTotal, 
      OpeningBalance
    FROM Customer c
    JOIN Payment i ON c.CustomerId = i.CustomerId

UNION ALL

    SELECT
      c.CustomerId,
      CustomerName, 
      ReturnInvoiceDate,
      'ReturnInvoice No. ' + CAST((ROW_NUMBER() OVER (PARTITION BY c.CustomerId ORDER BY c.RegistrationDate, ReturnInvoiceDate)) AS VARCHAR(10)) AS Description,
      0, 
      -1.0 * ReturnInvoiceTotal, 
      OpeningBalance
    FROM Customer c
    JOIN ReturnInvoice i ON c.CustomerId = i.CustomerId

UNION ALL

    SELECT 
      c.CustomerId,
      CustomerName, 
      ReturnPaymentDate, 
      'Return Payment No. ' + CAST((ROW_NUMBER() OVER (PARTITION BY c.CustomerId ORDER BY c.RegistrationDate, ReturnPaymentDate)) AS VARCHAR(10)) AS Description,
      ReturnPaymentTotal, 
      0, 
      OpeningBalance
    FROM Customer c
    JOIN ReturnPayment i ON c.CustomerId = i.CustomerId
) u

ORDER BY [Date]

Here is a SQLFiddle

2
  • how can i get the order of tale wise insertion. for example 1st i have entered invoice after i have entered payment after that again payment after insert new invoice like that order under date wise. display the output. is that possible? code example Please with above table..
    – M.I.M.F
    Commented Apr 3, 2020 at 20:10
  • when filter date wise opening balance getting starting balance. when retrieve data from date wise opening balance should be before date outstanding. how can get that?
    – M.I.M.F
    Commented Feb 24, 2023 at 6:22

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.