Empresas
Empleos
  • Sobre nosotros
  • Soluciones
    • Publicación de vacantes
      Publica tu vacante y recibe candidatos calificados en 48h.
    • Evaluación de candidatos
      500+ pruebas técnicas y psicológicas, más anti-fraude.
    • Headhunting
      Búsqueda ejecutiva a la medida de principio a fin.
    • Nómina + EOR
      Dispersión de nómina y EOR en más de 15 países de LATAM.
  • Precios
  • Empleos

0

207
Vistas
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 Respuestas
Responde la pregunta

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 Denunciar
Responde la pregunta
Encuentra empleos remotos

¡Descubre la nueva forma de encontrar empleo!

Top de empleos
Top categorías de empleo
Empresas
Publicar vacante Precios Comercial
Legal
Términos y condiciones Política de privacidad
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Recomiéndame algunas ofertas
Necesito ayuda