left join on the first appeared row by datetime-CodePudding

If classes 'Math' or 'Biology' have flag is NULL or = 0, we need to left join with the first appeared (according to the date) classes 'Literature' or 'English Literature' from the below.

If classes 'Math' or 'Biology' have flag = 1 then we need to left join with the most recent (according to the date) classes 'Literature' or 'English Literature'

Initial table:

| class              | date                    | flag |
| ------------------ | ----------------------- | ---- |
| Math               | 2020-07-07 20:08:00.000 | 0    |
| Biology            | 2020-07-07 21:08:00.000 | 1    |
| Math               | 2020-07-08 17:08:00.000 | NULL |
| English            | 2020-07-10 13:08:00.000 | 0    |
| Literature         | 2020-07-15 20:08:00.000 |      |
| English Literature | 2020-07-15 21:08:00.000 |      |
| Math               | 2020-07-15 22:08:00.000 | 1    |
| Literature         | 2020-09-09 20:08:00.000 |      |
| Math               | 2020-09-16 11:08:00.000 | 1    |
| English            | 2020-09-17 13:18:00.000 | 0    |
| Biology            | 2020-09-19 13:18:00.000 | NULL |

Result table (contain class without class = 'Literature', 'English Literature'):

| class   | date                    | flag | class_2            | date_2                  |
| ------- | ----------------------- | ---- | ------------------ | ----------------------- |
| Math    | 2020-07-07 20:08:00.000 | 0    | Literature         | 2020-07-15 20:08:00.000 |
| Biology | 2020-07-07 21:08:00.000 | 1    |                    |                         |
| Math    | 2020-07-08 17:08:00.000 | NULL | Literature         | 2020-07-15 20:08:00.000 |
| English | 2020-07-10 13:08:00.000 | 0    |                    |                         |
| Math    | 2020-07-15 22:08:00.000 | 1    | English Literature | 2020-07-15 21:08:00.000 |
| Math    | 2020-09-16 11:08:00.000 | 1    | Literature         | 2020-09-09 20:08:00.000 |
| English | 2020-09-17 13:18:00.000 | 0    |                    |                         |
| Biology | 2020-09-19 13:18:00.000 | NULL |                    |                         |

Explanations:

Biology at 2020-09-19 13:18:00.000 doesn't have a join because it has flag NULL and below (later than 'Biology') there are no rows with 'Literature' and 'English Literature' according to the rules.
Biology at 2020-07-07 21:08:00.000 with flag 1 also doesn't have a join because there was no ' English Literature' or ' Literature' before

3) Math at 2020-07-07 20:08:00.000 with flag 0 (it is the first row) . As the flag is 0 so we look at the first appeared 'Literature' or 'English Literature' class after this class. The first appeared is 'Literature' at 2020-07-15 20:08:00.000 so we match them

How to do LEFT JOIN with finding recent or first row according to the date?

CodePudding user response：

Without claiming that it is the most elegant solution; this would work:

with Tbl as (
    select Class, Date=cast(date as datetime), flag
    from (values
     ('Math',' 2020-07-07 20:08:00.000 ', 0    )
    ,('Biology',' 2020-07-07 21:08:00.000 ', 1    )
    ,('Math',' 2020-07-08 17:08:00.000 ', NULL )
    ,('English',' 2020-07-10 13:08:00.000 ', 0    )
    ,('Literature',' 2020-07-15 20:08:00.000 ',NULL      )
    ,('English Literature',' 2020-07-15 21:08:00.000 ',NULL      )
    ,('Math',' 2020-07-15 22:08:00.000 ', 1    )
    ,('Literature',' 2020-09-09 20:08:00.000 ',NULL      )
    ,('Math',' 2020-09-16 11:08:00.000 ', 1    )
    ,('English',' 2020-09-17 13:18:00.000 ', 0    )
    ,('Biology',' 2020-09-19 13:18:00.000 ', NULL )
    ) T(Class, date, flag)
),
T as (
 select 
       Tbl.*
     , Case when Class in ('Math','Biology') then 'MB' 
            when Class in ('Literature','English Literature') then 'LE'
            else 'Others'
       end as ClassGroup
 from Tbl
)
select *
from (select Ta.*
             , Tb.Class as B_Class
             , Tb.date as B_Date
             , Tb.flag as B_Flag
             , SeqAsc=row_number() over (partition by ta.ClassGroup, ta.Date order by tb.Date asc) 
             , SeqDesc=row_number() over (partition by ta.ClassGroup, ta.Date order by tb.Date desc) 
        from T as Ta
             left join
             T as Tb
             on Ta.ClassGroup='MB'
                and Tb.ClassGroup='LE'
                and ((coalesce(Ta.flag,0)=0 and Ta.date <= Tb.date) 
                     OR
                     (coalesce(Ta.flag,0)=1 and Ta.date >= Tb.date)
                    )
        where Ta.ClassGroup in ('MB','Others')
     ) T
where 
 (coalesce(T.flag,0)=0 and SeqAsc=1)
 or (coalesce(T.flag,0)=1 and SeqDesc=1)
 or B_Class is null
 order by Date, class

You have some blank flag values, I assumed NULL, You have not indicated which database products you used, I assumed SqlServer (but it should work with majority of them).