Home How to create a column where the first value is equal to the second value of another column?

Questions

How to create a column where the first value is equal to the second value of another column?

June 23, 2023

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.

Thank you

>Solution :

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 (order by id) as end_date
    from test
) as t;

You can also check the fiddle i have created HERE