Inferring a duration (time) value from two datetime values in separate rows

Question

Consider the following schema definition for table MyTable

TABLE MyTable

[Id] [nvarchar](max) NOT NULL, -- This is not really a nvarchar type, just simplifying the schema
[PropertyName] [nvarchar](max) NOT NULL,
[OriginalValue] [nvarchar](max) NOT NULL,
[UpdatedValue] [nvarchar](max) NULL,
[ChangeTimestamp] [datetime] NULL

A data example of this table would be:

Id	PropertyName	OriginalValue	UpdatedValue	ChangeTimestamp
Id1	Property1	Value2	Value3	2022-11-02 02:00:00.000
Id1	Property1	Value1	Value2	2022-11-02 01:00:00.000

What I'm aiming to do is to create a view that will define a new column [duration] that will give the time a particular setting was activated. Taking the example above, we can see that Value2 was ON for 1hour.

If anyone has done anything similar in the past, I would appreciate some suggestions.

Answer 1

If you just want the time difference between consecutive rows, you can use window functions.

For the duration to show up on the row where a setting is deactivated, use lag():

select t.*,
    datediff(
        second, 
        lag(changeTimestamp) over(partition by id, property order by changeTimestamp), 
        changeTimestamp
    ) duration
from mytable t

If you prefer to show the duration ar activation time, then you can look ahead with lead:

select t.*,
    datediff(
        second, 
        changeTimestamp, 
        lead(changeTimestamp) over(partition by id, property order by changeTimestamp)
    ) duration
from mytable t
    

Note that I assumed that you want to partition your data by group of rows sharing the same id and property. You might want to review that.