WITH Alarm (C1, C1Alarm, C2, C2Alarm, C3, C3Alarm, C4, C4...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Anime and Manga

Q&A for anime and manga fans

-- Enter Query Title
-- Enter Query Description
WITH Alarm (C1, C1Alarm, C2, C2Alarm, C3, C3Alarm, C4, C4Alarm) AS (
    SELECT  12.44, 0, 99.43, 0, 4.43, 1, 43.33, 0
    UNION ALL SELECT 12.44, 1, 99.43, 0, 4.43, 1, 43.33, 0
)
, RowNumbers AS (
    SELECT  rn = ROW_NUMBER() OVER (ORDER BY C1)
            , C1, C1Alarm
            , C2, C2Alarm
            , C3, C3Alarm
            , C4, C4Alarm
    FROM    Alarm   
)
, q AS (
    SELECT  rn, rng = C1 FROM RowNumbers WHERE C1Alarm = 0
    UNION ALL SELECT rn, C2 FROM RowNumbers WHERE C2Alarm = 0
    UNION ALL SELECT rn, C3 FROM RowNumbers WHERE C3Alarm = 0
    UNION ALL SELECT rn, C4 FROM RowNumbers WHERE C4Alarm = 0
)
SELECT  C1, C1Alarm
        , C2, C2Alarm
        , C3, C3Alarm
        , C4, C4Alarm
        , rng.range
FROM    RowNumbers rn
        LEFT OUTER JOIN (       
            SELECT  rn, range = MAX(rng) - MIN(rng)     
            FROM    q                   
            GROUP BY
                    rn
        ) rng ON rng.rn = rn.rn

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results