Search code examples
sqlsap-iq

SQL query to get each EID's & JOINING_DT corresponding records in tab2 which are just previous, equal date and all greater date rows from tab2

enter image description heretab1: 

EID    JOINING_DT
---------
1     04/04/2018
2     06/06/2018
3     04/04/2018
4     03/03/2018

tab2:

EID  JOINING_DT  INFO
-------------------
1   01/01/2018   x
1   02/02/2018   x
1   03/03/2018   x
1   04/04/2018   x
1   05/05/2018   x
2   01/01/2018   x
2   05/05/2018   x
2   07/07/2018   x
3   02/02/2018   x
4   03/03/2018   x

By using above 2 tables i want to get t1.id = t2.d and date of t1 should be just prior, equal and all greater date. like below Result: 

EID  JOINING_DT  INFO
-------------------
1   03/03/2018   x
1   04/04/2018   x
2   01/01/2018   x
2   05/05/2018   x
2   07/07/2018   x
3   02/02/2018   x
4   03/03/2018   x

Note: Please consider the solution should be high performance with high volume of data

Solution

  • Perhaps this is what you were looking for

          select id, date,
       value from
       table1 t1 join
       table2 t2 on 
        (  t1.id =t2.id )
         and
         (abs(t2.date-t1.date) =1
         or 
          t1.date=t2.date)