Business
Jobs
  • About Us
  • Solutions
    • Job Postings
      Post your job and receive qualified candidates in 48h.
    • Candidate Assessments
      500+ technical and psychological tests, plus anti-fraud.
    • Headhunting
      Tailor-made executive search from start to finish.
    • Payroll + EOR
      Payroll dispersal and EOR across 15+ LATAM countries.
  • Pricing
  • Jobs

0

195
Views
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.stack.imgur.com/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
over 4 years ago · Santiago Trujillo
1 answers
Answer question

0

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  |
over 4 years ago · Santiago Trujillo Report
Answer question
Find remote jobs

Discover the new way to find a job!

Top jobs
Top job categories
Business
Post vacancy Pricing Sales
Legal
Terms and conditions Privacy policy
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Show me some job opportunities
There's an error!