CODEWITHSUNDEEP
sqlsql-serversql-server-2016
GoAnatol
GoAnatol

Reputation: 21

SQL Server : convert historical data to Temporal Tables

We have SQL Server 2016. For many years we have our own history. Now I want to convert historical data to a new format, and use the CustomAudit tables as a history table with already available historical data.

For the beginning there will be a small example, then the question

CREATE TABLE [dbo].[client]
(
     idclient int identity(1,1) primary key, 
     clientData nvarchar (400)
) ON [PRIMARY] 

INSERT [dbo].[client] ( clientData ) values ('some-12221')
INSERT [dbo].[client] ( clientData ) values ('some-22111')

alter table [client] 
add 
    StartTime datetime2 GENERATED ALWAYS AS ROW START  DEFAULT GETUTCDATE(), 
    EndTime datetime2 GENERATED ALWAYS AS ROW END DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'), 
    PERIOD FOR SYSTEM_TIME (StartTime,EndTime) 

CREATE TABLE [dbo].[CustomAydit_client]
(
    idclient int,
    dEditDate datetime NOT NULL DEFAULT (getdate()), 
    clientData nvarchar (400)
) ON [PRIMARY] 

alter table [CustomAydit_client] 
add 
    StartTime datetime2 GENERATED ALWAYS AS ROW START  DEFAULT GETUTCDATE(), 
    EndTime datetime2 GENERATED ALWAYS AS ROW END DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'), 
    PERIOD FOR SYSTEM_TIME (StartTime,EndTime)

INSERT [dbo].[CustomAydit_client] (idclient, dEditDate, clientData) 
VALUES (1, (CAST(N'2016-05-06 10:08:11.923' AS DateTime)), 'some-1')

INSERT [dbo].[CustomAydit_client] (idclient, dEditDate, clientData) 
VALUES (1, (CAST(N'2016-02-11 10:08:11.923' AS DateTime)), 'some-211')

INSERT [dbo].[CustomAydit_client] (idclient, dEditDate, clientData) 
VALUES (2, (CAST(N'2016-12-06 10:08:11.923' AS DateTime)), 'some-1')

INSERT [dbo].[CustomAydit_client] (idclient, dEditDate, clientData) 
VALUES (1, (CAST(N'2015-05-19 10:08:11.923' AS DateTime)), 'some-1')

INSERT [dbo].[CustomAydit_client] (idclient, dEditDate, clientData) 
VALUES (2, (CAST(N'2016-05-06 10:08:11.923' AS DateTime)), 'some-211')

INSERT [dbo].[CustomAydit_client] (idclient, dEditDate, clientData)
VALUES (1, (CAST(N'2016-05-26 10:08:11.923' AS DateTime)), 'some-1')

INSERT [dbo].[CustomAydit_client] (idclient, dEditDate, clientData) 
VALUES (2, (CAST(N'2016-05-06 10:08:11.923' AS DateTime)), 'some33-1')

INSERT [dbo].[CustomAydit_client] (idclient, dEditDate, clientData) 
VALUES (1, (CAST(N'2016-05-06 10:08:11.923' AS DateTime)), 'some3-1')

INSERT [dbo].[CustomAydit_client] (idclient, dEditDate,clientData) 
VALUES (2, (CAST(N'2016-11-16 10:08:11.923' AS DateTime)), 'some-1')

INSERT [dbo].[CustomAydit_client] (idclient, dEditDate, clientData) 
VALUES (1, (CAST(N'2016-02-17 10:08:11.923' AS DateTime)), 'some-1')

I need StartTime for row AND for row.

For customer 1, I want to take row 2 data of field DeditDate

Update this data row 1 field EndTime

And the last line, the client should not have EndTime, this ok 9999-12-31 23:59:59.9999999

SELECT
    ROW_NUMBER() OVER( PARTITION BY idclient ORDER BY dEditDate) AS tempid,
    idclient,
    dEditDate,
    StartTime, EndTime
FROM
    [dbo].[CustomAydit_client]
ORDER BY
    idclient, dEditDate

Upvotes: 2

Views: 349

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can use the lead function to get a value from the next row as endtime.

select 
    ROW_NUMBER() OVER(PARTITION BY idclient ORDER BY dEditDate) AS tempid,
    idclient,
    dEditDate,
    StartTime,
    lead(dEditDate) over(PARTITION BY idclient ORDER BY dEditDate) as EndTime
from [dbo].[CustomAydit_client]
order by idclient, dEditDate

Upvotes: 1

Related Questions