Filtering out duplicate values at runtime in a sql database - set based

Question

I have a database issue that i currently cannot wrap my head around with an easy solution. In my db I have a table that stores event values.. 0's and 1's with a timestamp. Issue being that it is possible for there to be the same event to occur twice as a business rule. Like below

• '2008-09-22 16:28:14.133', 0
• '2008-09-22 16:28:35.233', 1
• '2008-09-22 16:29:16.353', 1
• '2008-09-22 16:31:37.273', 0
• '2008-09-22 16:35:43.134', 0
• '2008-09-22 16:36:39.633', 1
• '2008-09-22 16:41:40.733', 0

in real life these events are cycled and I’m trying to query over to get the cycles of these but I need to ignore the duplicate values ( 1,1 ) the current solution is using a SQL cursor to loop each and throw out the value if the previous was the same. I’ve considered using a trigger on the insert to clean up in a post processed table but I can’t think of an easy solution to do this set based.

Any ideas or suggestions?

Thanks

Answer 1

This uses a SQL Server Common Table Expression, but it can be inlined, with table t with columns dt and cyclestate:

;WITH Firsts AS (
    SELECT t1.dt
        ,MIN(t2.dt) AS Prevdt
    FROM t AS t1
    INNER JOIN t AS t2
        ON t1.dt < t2.dt
        AND t2.cyclestate <> t1.cyclestate
    GROUP BY t1.dt
)
SELECT MIN(t1.dt) AS dt_start
    ,t2.dt AS dt_end
FROM t AS t1
INNER JOIN Firsts
    ON t1.dt = Firsts.dt
INNER JOIN t AS t2
    ON t2.dt = Firsts.Prevdt
    AND t1.cyclestate <> t2.cyclestate
GROUP BY t2.dt
    ,t2.cyclestate
HAVING MIN(t1.cyclestate) = 0