Comparing value to that of previous row depending on different field

Question

I have SQL which outputs rows of a date time stamp and a status change flag (0 or 1)

I need to get the timespan from the first record, where the flag will be 0, to when the status flag changes to 1, ignore records when the flag is still at 1, then get the time span after it changes back to 0 till the last record. The status change flag may flip between 0 and 1 any number of times.

So i need to be able to compare the status change flag to the previous row, and decide whether I need to keep accumulating the difference in the date time stamps.

I have been looking into writing a cursor but keep reading about how cursors are horribly inefficient.

Hopes this make any sense.

If you are on SQL Server 2012, you can use the `lag()` function to access values from the "previous" row. — , Apr 08 '13 at 14:32

bummi · Accepted Answer · 2013-04-08T15:51:54.263

DECLARE @test TABLE ([group] int,t DateTime,[status] bit)

INSERT INTO @test values (1,'20130101 11:11:11',0)
INSERT INTO @test values (1,'20130101 11:11:12',0)
INSERT INTO @test values (1,'20130101 11:11:13',0)
INSERT INTO @test values (1,'20130101 11:11:14',1)
INSERT INTO @test values (1,'20130101 11:11:15',1)
INSERT INTO @test values (1,'20130101 11:11:16',1)
INSERT INTO @test values (1,'20130101 11:11:17',0)
INSERT INTO @test values (1,'20130101 11:11:18',0)
INSERT INTO @test values (1,'20130101 11:11:19',0)


Select [Group],MIN(t)

,(Select MAX(t) from @test t2 where [status]=0 and t2.[group]=t.[group] and Exists(Select * from @test t3 where [status]=1 and t3.[group]=t.[group] and  t3.t<t2.t))
,DateDiff(ss,MIN(t)
,(Select MAX(t) from @test t2 where [status]=0 and t2.[group]=t.[group] and Exists(Select * from @test t3 where [status]=1 and t3.[group]=t.[group] and  t3.t<t2.t))
) as Seconds
from @test t where Status=0
group by [group]

Thanks, am just trying to modify my own sql to try to get this to work — atamata, Apr 08 '13 at 14:48

score 0 · Answer 2 · answered Apr 08 '13 at 14:20

0

I think something like this will work. But I might need more info on the table structure

WITH FirstFlag(FlagType, FlagTime)
AS
(
    SELECT 
        FlagType
        , min(DateCreated) as FlagTime
    FROM TheTable
    WHERE Flag = 0
)
, SecondFlag(FlagTime1, FlagTime2)
AS
(
    SELECT 
        F.FlagTime as FlagTime
        , min(T.DateCreated) as FlagTime
    FROM TheTable as T
        INNER JOIN FirstFlag as F
            ON T.FlagType = F.FlagType
    WHERE Flag = 1
        AND T.DateCreated > F.FlagTime
)
SELECT datediff(min, FlagTime1, FlagTime2)
FROM SecondFlag

answered Apr 08 '13 at 14:20

Mike Hewitt

682
5
11

This is actually related to another question I posted last month - http://stackoverflow.com/questions/15499675/denormalising-a-fully-normalised-table – atamata Apr 08 '13 at 14:48
Hi, well it's actually just a view with two id fields, datetime stamp and flag. – atamata Apr 08 '13 at 15:40

Comparing value to that of previous row depending on different field

2 Answers2