How to Retrieve Credit Debit and Balance From More Than One Table in SQL Server

Question

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,

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

Answer 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