Empresas
Empregos
  • Sobre nós
  • Soluções
    • Publicação de vagas
      Publique sua vaga e receba candidatos qualificados em 48h.
    • Avaliações de candidatos
      Mais de 500 testes técnicos e psicológicos, mais anti-fraude.
    • Headhunting
      Busca executiva personalizada do início ao fim.
    • Folha de Pagamento + EOR
      Dispersão de folha e EOR em mais de 15 países da LATAM.
  • Preços
  • Empregos

0

209
Visualizações
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 Respostas
Responde à pergunta

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 Relatório
Responde à pergunta
Encontrar trabalhos remotos

Descubra a nova forma de encontrar um emprego!

melhores empregos
Principais categorias de trabalho
Empresas
Postar vaga Preços Comercial
Jurídico
Termos e Condições Política de privacidade
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Recomende algumas ofertas para mim
Preciso de ajuda