CODEWITHSUNDEEP
mysqlsqldatewindow-functionsrecursive-query
Bharath
Bharath

Reputation: 31

How to pull the data based on two dates in sql

Need to display only the records which are created after the expiry of the previous request id.

https://i.sstatic.net/jUm97.png

I/P - 

REQUEST_ID  CREATED_DATE    EXPIRY_DATE
1           2020-06-02      2020-06-06  
2           2020-06-04      2020-06-10  
3           2020-06-08      2020-06-12  
4           2020-06-09      2020-06-16  
5           2020-06-09      2020-06-15              
6           2020-06-18      2020-06-20              
7           2020-06-21      2020-06-25

O/P - 

REQUEST_ID  CREATED_DATE    EXPIRY_DATE
    1       2020-06-02      2020-06-06
    3       2020-06-08      2020-06-12
    6       2020-06-18      2020-06-20
    7       2020-06-21      2020-06-25

Upvotes: 2

Views: 32

Answers (1)

GMB
GMB

Reputation: 222442

If you are running MySQL 8.0, you can do this with window functions:

select request, created_at, expiry_date
from (
    select 
        t.*,
        lag(expiry_date) over(order by request) lag_expiry_date
    from mytable t
) t
where lag_expiry_date is date or created_at > lag_expiry_date

On the other hand, if you want to incrementally select records based on the difference with the last valid record, then it is a bit more complicated. You need some kind of iterative process, which suggests a recursive query. Basically, you can enumerate all the rows with row_number(), and then process them one by one, deciding which one will be kept.

with recursive 
    data as (
        select t.*, row_number() over(order by created_date) rn from mytable t
    ),
    cte as (
        select d.*, 1 to_keep, expiry_date last_expiry_date 
        from data d 
        where rn = 1 
        union all
        select 
            d.*,
            (d.created_date > c.last_expiry_date),
            case when d.created_date > c.last_expiry_date 
                then d.expiry_date 
                else c.last_expiry_date
            end
        from cte c
        inner join data d on d.rn = c.rn + 1
    )
select request_id, created_date, expiry_date 
from cte 
where to_keep
order by rn

Demo on DB Fiddle:

| request_id | created_date | expiry_date |
| ---------- | ------------ | ----------- |
| 1          | 2020-06-02   | 2020-06-06  |
| 3          | 2020-06-08   | 2020-06-12  |
| 6          | 2020-06-18   | 2020-06-20  |
| 7          | 2020-06-21   | 2020-06-25  |

Upvotes: 1

Related Questions