0

I have a 3 tables such as "BASE_Customer", "BASE_Invoice" and "BASE_Payment" the below structure,

enter image description here

Query:

CREATE TABLE BASE_Customer
(
    CustomerId INT IDENTITY(1,1),
    CustomerName VARCHAR(45)
    PRIMARY KEY(CustomerId)
)
INSERT INTO BASE_Customer (CustomerName) VAlUES ('LEE')

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

CREATE TABLE BASE_Payment
(
    PaymentId INT IDENTITY(1,1),
    PaymentDate DATE,
    CustomerId INT NOT NULL,
    InvoiceId INT NULL,
    PaymentMethod VARCHAR(45) NOT NULL, -- CASH or CREDIT, ADVANCE
    Amount MONEY,
    PRIMARY KEY(PaymentId)
)
INSERT INTO BASE_Payment (PaymentDate, CustomerId, InvoiceId, PaymentMethod, Amount) VAlUES ('2020-01-16', 1, 1, 'CASH', 1000);
INSERT INTO BASE_Payment (PaymentDate, CustomerId, PaymentMethod, Amount) VAlUES ('2020-01-16', 1, 'CREDIT', 500);
INSERT INTO BASE_Payment (PaymentDate, CustomerId, InvoiceId, PaymentMethod, Amount) VAlUES ('2020-01-18', 1, 4, 'ADVANCE', 500);
INSERT INTO BASE_Payment (PaymentDate, CustomerId, PaymentMethod, Amount) VAlUES ('2020-01-18', 1, 'CREDIT', 2000);
INSERT INTO BASE_Payment (PaymentDate, CustomerId, InvoiceId, PaymentMethod, Amount) VAlUES ('2020-01-18', 1, 5, 'CASH', 150);
INSERT INTO BASE_Payment (PaymentDate, CustomerId, PaymentMethod, Amount) VAlUES ('2020-01-18', 1, 'CREDIT', 5000);
INSERT INTO BASE_Payment (PaymentDate, CustomerId, PaymentMethod, Amount) VAlUES ('2020-01-19', 1, 'CREDIT', 1200);
INSERT INTO BASE_Payment (PaymentDate, CustomerId, InvoiceId, PaymentMethod, Amount) VAlUES ('2020-01-19', 1, 7, 'ADVANCE', 500)
INSERT INTO BASE_Payment (PaymentDate, CustomerId, InvoiceId, PaymentMethod, Amount) VAlUES ('2020-01-19', 1, 8, 'CASH', 1000);
INSERT INTO BASE_Payment (PaymentDate, CustomerId, InvoiceId, PaymentMethod, Amount) VAlUES ('2020-01-20', 1, 9, 'ADVANCE', 750);
  • Customer table should Join to both tables "BASE_Invoice" and "BASE_Payment" (CustomerId is Foreign Key on both tables)
  • when inserting a data into "BASE_Invoice" table if invoice method column CASH at the time Invoice Payment inserting with payment method CASH,

  • when inserting a data into "BASE_Invoice" table if invoice method column CREDIT some times we insert payment with payment method ADVANCE or otherwise we don't insert payment on payment table.

  • on payment table only can insert CREDIT payment methods

I need to merge all the tables once and retrieve the ORDER of output such as below screen shot,

enter image description here

The issue is how to merge tables and get the Order of transaction

3

Databases do not store data "in order" (for the most part).

If you want data out in a specific order, you must supply an appropriate ORDER BY clause.


In your data model:

  • BASE_Invoice is a transaction.
  • BASE_Payment is a transaction.

You're missing a table that holds the information for a transaction. That table will include all columns common to both types of transactions, a transaction ID (PK) for all types of transactions, and information about the transaction that can be used by the ORDER BY clause.

It some cases, the Transaction ID could be used to determine ORDER BY. In other cases, you may need to include a timestamp column to be used by the ORDER BY clause.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.