CODEWITHSUNDEEP
t-sql
Ådne
Ådne

Reputation: 13

How to transform timestamp column into intervals with start and stop

I have the following table 

id          ts
----------- -----------
30          0
30          1205280000
30          2147483647
31          0
31          2147483647
...         ...

and I'm trying to transform it into a type 2 slowly changing dimension table for use in an OLAP application.

id          start       stop
----------- ----------- -----------
30          0           1205280000
30          1205280000  2147483647
31          0           2147483647
...         ...         ...

The timestamps 0 and 2147483647 could be considered constants since they represent the beginning and end of time (in UNIX epoch)

How can it be done?

Upvotes: 1

Views: 130

Answers (1)

Quassnoi
Quassnoi

Reputation: 425251

WITH    q AS
        (
        SELECT  id, ts, ROW_NUMBER() OVER (PARTITION BY id ORDER BY ts) AS rn
        FROM    mytable
        )
SELECT  q1.id, q1.ts AS start, q2.ts AS stop
FROM    q q1
JOIN    q q2
ON      q2.id = q1.id
        AND q2.rn = q1.rn + 1

Upvotes: 1

Related Questions