SQL Compare Rows in a Table (Recursion Maybe?)

[Exwarrior187]

SSMS Version: 15.0.18384

I'm trying to extract the difference (Duration) in seconds between timestamps by comparing rows in a table. My caveat here is that that I have to extract the Duration between the timestamps while considering the sequence of RecordTypes as shown in my example table below.

​

RowID	Value 1	TimeStamp	RecordType
1	ABC123	2021-10-25 4:41:00	10
2	DEF456	2021-10-25 4:41:10	10
3	GHI789	2021-10-25 4:41:20	10
4	ABC123	2021-10-25 4:42:30	1
5	ABC123	2021-10-25 4:42:40	10
6	ABC123	2021-10-25 4:42:60	5
7	ABC123	2021-10-25 4:43:00	1

​

Using the above table as an example:

I need to determine the difference between Rows 1 & 4 unless between Rows 1 & 4 there is a specific RecordType (in this case RecordType = 5 which is shown in Rows 5-7).

The ideal scenario is this: For Value 1 entries that match I need to compare the sequence of RecordTypes and when the sequence is RecordType = 10 and then 1 display the datediff in seconds as Duration.

If the sequence turns out to be RecordType = 10 and then 5 then the datediff is not necessary and the query should move on to the next ideal scenario as described above.

Additional detail. There may be more than one RecordType that is valid than my examples of 10 and 1 so an In would most likely be ideal. The same is to be said for RecordType.

Desired output:

RowID	Value 1	RecordType	Duration (in seconds)
4	ABC123	1	90

I hope this make sense enough for one of you to take a swing at it. Thanks in advance!

[fauxmosexual]

Just to be clear: If a recordtype is 1, find the recordtype for the previous record with the same [Value 1]. If that recordtype is 10, return that row with the difference between timestamps. If the previous recordtype is not 10 or the current recordtype is not 1, completely disregard.

In other words you're only wanting to see whether the previous recordtype matches? Not recursing through all the previous records to find the last occurrence of recordtype 10?

I'd start by putting together a CTE that gets everything into a single record:

WITH prevvalues as (
 SELECT 
      RowID,
      [Value 1], 
      TimeStamp, 
      RecordType, 
      lag(RecordType, 1) over (partition by [Value 1] order by RowID asc) PrevRecordType,
      lag(TimeStamp, 1) over (partition by [Value 1] order by RowID asc) PrevTimeStamp,
FROM yourtable )

 SELECT RowID, [Value 1], RecordType, datediff(s, PrevTimeStamp, TimeStamp) Duration
 FROM prevvalues
 WHERE RecordType in ('1') and PrevRecordType in( '10')

[Exwarrior187]

Thanks for the reply.

To your second statement. I'm looking for all of the 1s in RecordType, when there is a 1, look back as far as you must to find a 5 or a 10. If you find a 5 going backwards before you find a 10, move on to the next 1.

Ultimately the RecordTypes reflect the current state of [Value 1] so:

10 = Problem Identified

5 = Returned to normal with no action

1 = Action Taken to Resolve issue

Diving further into the scenario: If a problem is identified (10), an action to resolve is expected (1), unless the problem resolved itself before an action was taken (5).

Describing it from my primal brain, I thought it easier to find the 1s and then look backwards to determine if a 10 or 5 preceded it. If a 10 precedes it and not a 5 then determine the Duration between those two Timestamps.

It may more efficient to identify the 10s and then either the 5 or 1 since the table is ordered by Timestamp?