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.
Best Answer
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 insideDescription
. 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:
Here is a SQLFiddle