CODEWITHSUNDEEP
sqljoinsql-server-ceunion
Sachith
Sachith

Reputation: 201

How to join 2 columns as one and order by date?

I'm using SQL Server Compact Edition 4.0 and there are 2 tables called debit and credit as below.

tbl_debit

invoice | dealer | price| purchasedate
=========================================
  001   |  AAA   |  1000  | 2/9/2016 8:46:38 PM  
  002   |  AAA   |  1500  | 2/20/2016 8:46:38 PM

tbl_credit

 dealer | settlement| purchasedate
=========================================
  AAA   |    800    | 2/12/2016 8:46:38 PM  
  AAA   |    400    | 2/22/2016 8:46:38 PM

I want to create single table that should include 4 column..

 Invoice, Dealer, Amount, date

Amount should include both settlement from tbl_credit and price from tbl_debit and need to order by date.

I really appreciate if anyone can help me.

Upvotes: 1

Views: 57

Answers (1)

Dale Kube
Dale Kube

Reputation: 1460

Here's a script to logically approach the problem based on the limited information presented to us:

SELECT A.invoice, A.dealer, A.amount, A.purchasedate
        FROM (SELECT A.invoice, A.dealer, A.price [amount], A.purchasedate
        WHERE tbl_debit A
        UNION
        SELECT ' ', B.dealer, B.settlement, B.purchasedate
        FROM tbl_credit B) A
        ORDER BY 4

Upvotes: 1

Related Questions