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.