SQL Server 2008 two tables with common dates field how to select by datewise from both tables

debugcn Published at Dev

Chelliah Chellamani

two tables with common field with dates Here i mentioned how i need output kindly any help me.

table1
table1.col1 table1.col2  table1.col2
a           ab           2015-05-01 00:00:00.000
as          as           2015-05-01 00:00:00.000
as          asasd        2015-05-01 00:00:00.000
asd         aa           2015-05-02 00:00:00.000
asd         asd          2015-05-04 00:00:00.000


table2
table2.col1   table2.col2  table2.col3 
asd           aasd         2015-05-01 00:00:00.000
asasd         asd          2015-05-01 00:00:00.000  
asd           asd          2015-05-04 00:00:00.000
asd           asd          2015-05-05 00:00:00.000
asd           asd          2015-05-31 00:00:00.000

i want select by dateswise

Date                      table1.col1 table2.col2  table2.col1   table2.col2
2015-05-01 00:00:00.000   a           ab           asd           aasd
                          as          as           asasd         asd
                          as          asasd        Null          Null 
2015-05-02 00:00:00.000   asd         aa           Null          NUll 
2015-05-04 00:00:00.000   asd         asd          Null          Null 
                          asd         asdas        Null          Null
2015-05-05 00:00:00.000   Null        Null         sdas          asds
                          Null        Null         adad          asda

Giorgi Nakeuri

With FULL JOIN:

DECLARE @t1 TABLE
    (
      col1 VARCHAR(20) ,
      col2 VARCHAR(20) ,
      col3 DATE
    )
INSERT  INTO @t1
VALUES  ( 'a', 'ab', '2015-05-01 00:00:00.000' ),
        ( 'as', 'as', '2015-05-01 00:00:00.000' ),
        ( 'as', 'asasd', '2015-05-01 00:00:00.000' ),
        ( 'asd', 'aa', '2015-05-02 00:00:00.000' ),
        ( 'asd', 'asd', '2015-05-04 00:00:00.000' )


DECLARE @t2 TABLE
    (
      col1 VARCHAR(20) ,
      col2 VARCHAR(20) ,
      col3 DATE
    )
INSERT  INTO @t2
VALUES  ( 'asd', 'aasd', '2015-05-01 00:00:00.000' ),
        ( 'asasd', 'asd', '2015-05-01 00:00:00.000' ),
        ( 'asd', 'asd', '2015-05-04 00:00:00.000' ),
        ( 'asd', 'asd', '2015-05-05 00:00:00.000' ),
        ( 'asd', 'asd', '2015-05-31 00:00:00.000' )


SELECT  ISNULL(t1.col3, t2.col3) as date,
        t1.col1 ,
        t1.col2 ,
        t2.col1 ,
        t2.col2
FROM    ( SELECT    * ,
                    ROW_NUMBER() OVER ( PARTITION BY col3 ORDER BY ( SELECT NULL) ) rn
          FROM      @t1) t1
        FULL JOIN 
        ( SELECT  * ,
                    ROW_NUMBER() OVER ( PARTITION BY col3 ORDER BY ( SELECT NULL) ) rn
            FROM    @t2) t2 ON t1.col3 = t2.col3 AND t1.rn = t2.rn

Output:

date        col1    col2    col1    col2
2015-05-01  a       ab      asd     aasd
2015-05-01  as      as      asasd   asd
2015-05-01  as      asasd   NULL    NULL
2015-05-02  asd     aa      NULL    NULL
2015-05-04  asd     asd     asd     asd
2015-05-05  NULL    NULL    asd     asd
2015-05-31  NULL    NULL    asd     asd

Collected from the Internet

Please contact [email protected] to delete if infringement.