0

Tables are INVOICE, PAYMENT, RETURN_INVOICE, RETURN_PAYMENT, etc in my database, i want to retrieve data CREDIT, DEBIT AND BALANCE customer wise and order should be DATE wise and table insertion order, for example 1st i have entered invoice after i have entered payment after that again payment after insert new invoice like that order

Invoice Table there are 2 types of Invoices "CASH" and "CREDIT" Invoice Payment Table there are 3 types of Payment methods "CASH", "ADVANCE", "CREDIT"

if Cash invoice made Cash Payment executing Same time. If Credit invoice made Advance payment may be or not executing same time (some times advance payment made otherwise total invoice should be credit).

below are tables and insert query statements,

CREATE TABLE Customer
(
    CustomerId INT IDENTITY(1,1),
    CustomerName VARCHAR(45),
    OpeningBalance MONEY,
    PRIMARY KEY(CustomerId)
)
INSERT INTO Customer (CustomerName, OpeningBalance) VAlUES ('DEMI', 1000)

CREATE TABLE Invoice
(
    InvoiceId INT IDENTITY(1,1),
    InvoiceDate DATE,
    CustomerId INT,
    InvoiceMethod VARCHAR(45) NOT NULL, -- CASH or CREDIT
    InvoiceTotal MONEY,
    PRIMARY KEY(InvoiceId)
)
INSERT INTO Invoice (InvoiceDate, CustomerId, InvoiceMethod, InvoiceTotal) VAlUES ('2020-01-16', 1, 'CASH', 1000);
INSERT INTO Invoice (InvoiceDate, CustomerId, InvoiceMethod, InvoiceTotal) VAlUES ('2020-01-16', 1, 'CREDIT', 2000);
INSERT INTO Invoice (InvoiceDate, CustomerId, InvoiceMethod, InvoiceTotal) VAlUES ('2020-01-17', 1, 'CREDIT', 500);
INSERT INTO Invoice (InvoiceDate, CustomerId, InvoiceMethod, InvoiceTotal) VAlUES ('2020-01-17', 1, 'CREDIT', 2000);
INSERT INTO Invoice (InvoiceDate, CustomerId, InvoiceMethod, InvoiceTotal) VAlUES ('2020-01-18', 1, 'CASH', 150);
INSERT INTO Invoice (InvoiceDate, CustomerId, InvoiceMethod, InvoiceTotal) VAlUES ('2020-01-18', 1, 'CREDIT', 1000);
INSERT INTO Invoice (InvoiceDate, CustomerId, InvoiceMethod, InvoiceTotal) VAlUES ('2020-01-22', 1, 'CREDIT', 2250);
INSERT INTO Invoice (InvoiceDate, CustomerId, InvoiceMethod, InvoiceTotal) VAlUES ('2020-01-24', 1, 'CREDIT', 1750);
INSERT INTO Invoice (InvoiceDate, CustomerId, InvoiceMethod, InvoiceTotal) VAlUES ('2020-01-28', 1, 'CREDIT', 3000);
INSERT INTO Invoice (InvoiceDate, CustomerId, InvoiceMethod, InvoiceTotal) VAlUES ('2020-01-28', 1, 'CREDIT', 1000);

CREATE TABLE Payment
(
    PaymentId INT IDENTITY(1,1),
    PaymentDate DATE,
    CustomerId INT,
    PaymentMethod VARCHAR(45) NOT NULL, -- CASH or CREDIT, ADVANCE
    PaymentTotal MONEY,
    PRIMARY KEY(PaymentId)
)
INSERT INTO Payment (PaymentDate, CustomerId, PaymentMethod, PaymentTotal) VAlUES ('2020-01-16', 1, 'CASH', 1000)
INSERT INTO Payment (PaymentDate, CustomerId, PaymentMethod, PaymentTotal) VAlUES ('2020-01-17', 1, 'ADVANCE', 250)
INSERT INTO Payment (PaymentDate, CustomerId, PaymentMethod, PaymentTotal) VAlUES ('2020-01-18', 1, 'CREDIT', 1500)
INSERT INTO Payment (PaymentDate, CustomerId, PaymentMethod, PaymentTotal) VAlUES ('2020-01-18', 1, 'CREDIT', 750)
INSERT INTO Payment (PaymentDate, CustomerId, PaymentMethod, PaymentTotal) VAlUES ('2020-01-18', 1, 'CASH', 150)
INSERT INTO Payment (PaymentDate, CustomerId, PaymentMethod, PaymentTotal) VAlUES ('2020-01-20', 1, 'CREDIT', 2000)
INSERT INTO Payment (PaymentDate, CustomerId, PaymentMethod, PaymentTotal) VAlUES ('2020-01-23', 1, 'CREDIT', 5000)
INSERT INTO Payment (PaymentDate, CustomerId, PaymentMethod, PaymentTotal) VAlUES ('2020-01-26', 1, 'CREDIT', 200)
INSERT INTO Payment (PaymentDate, CustomerId, PaymentMethod, PaymentTotal) VAlUES ('2020-01-28', 1, 'CREDIT', 500)
INSERT INTO Payment (PaymentDate, CustomerId, PaymentMethod, PaymentTotal) VAlUES ('2020-01-28', 1, 'ADVANCE', 250)

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)


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, (Order should be same like screen shot)

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.