CODEWITHSUNDEEP
sqlsql-serverwindow-functionsdate-arithmetic
Luis_DV
Luis_DV

Reputation: 83

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

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.

Upvotes: 0

Views: 38

Answers (1)

GMB
GMB

Reputation: 222582

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.

Upvotes: 1

Related Questions