user3600663 user3600663 - 6 months ago 9
SQL Question

SQL Logic to merge two tables based on the given scenario


  • Table 1 has Id with random date and the corresponding value.

  • Table 2 has id with sequence date (it’s not necessarily to be sequence).



Matching the Table2.Id and Table2.SequenceDate with Table1.Id and Table.RandomDate and then need to apply theTable1. value to the Table2 till the next Random Date occurs.

You can see in the below expected result

Table 1
RandomDate value ID
2/12/2016 A 1
2/15/2016 B 1
2/18/2016 C 1
2/12/2016 A 2

Table 2
SequenceDate ID
2/12/2016 1
2/13/2016 1
2/14/2016 1
2/15/2016 1
2/16/2016 1
2/17/2016 1
2/18/2016 1
2/19/2016 1
2/20/2016 1
2/12/2016 2


Expected Result from table and table 2
SequenceDate ID value
2/12/2016 1 A
2/13/2016 1 A
2/14/2016 1 A
2/15/2016 1 B
2/16/2016 1 B
2/17/2016 1 B
2/18/2016 1 C
2/19/2016 1 C
2/20/2016 1 C
2/12/2016 2 A

Answer

Finally I got that logic, thanks everyone for all your inputs.

       SELECT 
        t2.Id,
        t2.SequenceDate,
        t1.RandomDate,
        t1.Value,
        ISNULL(LEAD(RandomDate,1,NULL) OVER (PARTITION BY Id  ORDER BY Id,RandomDate),DATEADD(YEAR,99,RandomDate)) AS nextRandomDte,
        ISNULL(LAG(RandomDate,1,NULL) OVER (PARTITION BY Id  ORDER BY Id,RandomDate),RandomDate) AS prevRandomDte
    FROM 
          dbo.table1 t1
    RIGHT JOIN dbo.table2 t2 
           ON t1.Id = t2.Id
             AND ( t2.SequenceDate >= t2.RandomDate OR 
                     t2.RandomDate = prevRandomDte
                   )
             AND t2.SequenceDate <  nextRandomDte