0

I want to create a column where the first element is equal to the second element of another column for the same ID.The last date will be a default value.

For example, I have this table:

ID start_date
123 03/04/2022
123 06/04/2022
123 08/05/2022
123 10/05/2022
123 15/05/2022
123 20/05/2022

I want to get this output:

ID start_date end_date.
123 03/04/2022 06/04/2022
123 06/04/2022 08/05/2022
123 08/05/2022 10/05/2022
123 10/05/2022 15/05/2022
123 15/05/2022 20/05/2022
123 20/05/2022 01/01/1999

I don't have any idea how to do it.

Charlieface
  • 52,284
  • 6
  • 19
  • 43
data-geek
  • 3
  • 2

1 Answers1

2

Hi this is the query that answers your question (disregard my date format and use yours):

declare @fixeddate date
set @fixeddate='12/12/2024'

select Id, start_date, ISNULL(end_date, @fixeddate) end_date
from (
    select id,            
           start_date,
           lead(start_date) over (partition by id order by id) as end_date
    from test
) as t;

You can also check the fiddle i have created HERE

jmvcollaborator
  • 2,141
  • 1
  • 6
  • 17