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

Question

tab1:

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

Please format your data so that it is readable. Remove the images and instead include _text_. And also tell us which database you are using. "SQL" is just a language, not an actual product. — Tim Biegeleisen, Jan 06 '19 at 04:58
first of all name your columns correctly what the hell you have in tables structure multiple same columns — Himanshu, Jan 06 '19 at 05:10

Himanshu · Accepted Answer · 2019-01-06T05:10:13.680

1

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)

edited Jan 06 '19 at 05:10

answered Jan 06 '19 at 05:04

Himanshu

3,830
2
10
29

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

1 Answers1