joining two tables with date range

i have 2 tables.

Table A                      Table B  
 DATE         QTYA             DATE         QTYB  
--------------------         --------------------  
 Jan1          5                Jan1          6  
--------------------         --------------------  
 Jan2          10               Jan3          10  

I want an output like this. im using OLEDB.please help.TIA

Out Put
 DATE         QTYA             QTYB
 Jan1           5               6
 Jan2           10
 Jan3                           10

1 answer

  • answered 2018-02-13 01:31 Tim Biegeleisen

    You can achieve this with a combination of a union and a pivot query:

    SELECT
        DATE,
        MAX(CASE WHEN source = 'A' THEN QTY END) AS QTYA,
        MAX(CASE WHEN source = 'B' THEN QTY END) AS QTYB
    FROM
    (
        SELECT DATE, QTYA AS QTY, 'A' AS source
        FROM TableA
        UNION ALL
        SELECT DATE, QTYB, 'B'
        FROM TableB
    ) t
    GROUP BY DATE;
    

    This assumes that the dates you expect in your output appear in the A and/or B table. If you expect a certain coverage of dates which your data lacks, then you may have to create and join with a calendar table.