sql - Create column based on other column but with conditions

Question

I'm very new to SQL and I've I simply cannot work out a method for the following:

I have this table with start dates of the codes

Row     Code    Start Date      Product
1       A1      2020-01-01      X
2       A1      2020-05-15      Y
3       A2      2020-02-02      X
4       A3      2020-01-31      Z
5       A3      2020-02-15      Y
6       A3      2020-12-31      X

Ultimately I need to be able to query another table and find out what Product a code was on a certain date, so Code A1 on 2020-01-10 was = X, but Code A1 today is = Y

I think I can work out how to use a between statement in the where clause, but I cannot work out how to Alter the table to have an End Date so it looks like this:

Row     Code    Start_Date      Product     End_Date
1       A1      2020-01-01      X           2020-05-14
2       A1      2020-05-15      Y           NULL
3       A2      2020-02-02      X           NULL
4       A3      2020-01-31      Z           2020-02-14
5       A3      2020-02-15      Y           2020-12-30
6       A3      2020-12-31      X           NULL

Please note the database does not have an End_Date field

question from:https://stackoverflow.com/questions/65922087/create-column-based-on-other-column-but-with-conditions

Answer 1

I think you want lead():

select t.*,
       dateadd(day, -1,
               lead(start_date) over (partition by code order by start_date)
              ) as end_date
from t;

Note: I would recommend not subtracting one day for the end date, so the end date is non-inclusive. This makes the end date the same as the next start date, which I find is easier to ensure that there are no gaps or overlaps in the data.