Increment date by 1 day based on another column's date from previous record

All we need is an easy explanation of the problem, so here it is.

I am using SQL server 2008 R2 and I don´t have LAG and LEAD functions.

I have this inicial table and I need that StartDate is equal to EndDate from previous record plus 1 day.
See Result Table.

Increment date by 1 day based on another column's date from previous record

Regards,
Elio Fernandes

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

try this self join:

CREATE TABLE [dbo].[Table_1](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [date] [datetime2](5) NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)
) ON [PRIMARY]

some data:

SET IDENTITY_INSERT [dbo].[Table_1] ON 
INSERT [dbo].[Table_1] ([id], [date]) VALUES (1, CAST(N'2021-01-01T00:00:00.0000000' AS DateTime2))
INSERT [dbo].[Table_1] ([id], [date]) VALUES (2, CAST(N'2021-01-03T00:00:00.0000000' AS DateTime2))
INSERT [dbo].[Table_1] ([id], [date]) VALUES (3, CAST(N'2021-01-06T00:00:00.0000000' AS DateTime2))
INSERT [dbo].[Table_1] ([id], [date]) VALUES (4, CAST(N'2021-01-10T00:00:00.0000000' AS     DateTime2))
INSERT [dbo].[Table_1] ([id], [date]) VALUES (5, CAST(N'2021-01-20T00:00:00.0000000' AS DateTime2))
SET IDENTITY_INSERT [dbo].[Table_1] OFF

and this query:

select t.id,t.date as [from], tnext.date as [to]
from table_1 as t left join
     table_1 as tnext
     on t.id = tnext.id -1
     order by 1

Increment date by 1 day based on another column's date from previous record

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply