SQL Diff intervals

-- Time series ranking

-- Set up table with random data
USE pubs
SET NOCOUNT ON
GO

IF NULLIF(OBJECT_ID('measurements'), 0) > 0
    DROP TABLE measurements
GO

CREATE TABLE measurements
(
when_taken     datetime      NOT NULL,
temperature    numeric(4,1)  -- (Fahrenheit)
)
CREATE CLUSTERED INDEX measurements_idx01
    ON measurements (when_taken)
GO

DECLARE @counter int, @whendate datetime, @val numeric(4, 1),
    @randdiff smallint, @randmins smallint
SELECT @counter=1, @whendate=GETDATE(), @val=50.0
/* Insert 20 rows of data.  Change constant if you want more.   */
WHILE (@counter <= 20)
    BEGIN
    INSERT measurements VALUES (@whendate, @val)
    --  Get a random number between -20 and 20 for change in
    -- temperature. This will be added to the previous value ,
    -- plus RAND() again to give a fractional component.
        SELECT 
        @randdiff=CASE
        WHEN CONVERT(int, RAND() * 100) % 2 = 1 THEN
            CONVERT(int, RAND() * 1000) % 21 * -1
        ELSE CONVERT(int, RAND() * 1000) % 21
        END,
    -- Get a random number between 0 and 10080 (the number of mins
    -- in a week). This will be added to the current GETDATE()
    -- value. Since GETDATE() returns a value to the millisecond
    -- it's very unlikely there will ever be a duplicate, though it
    -- is possible if the result of the additon and the current 
    -- GETDATE() value happen to collide with the addition in
    -- another row. (It is intentional that we are
    -- not assuming that dups are automatically prevented.)
        @randmins=CONVERT(int, RAND() * 100000) % 10080
SELECT @counter=@counter + 1,
@whendate=DATEADD(mi, @randmins, GETDATE()),
    @val=@val + @randdiff + RAND()
    END

SELECT * FROM measurements
GO

if nullif(object_id('rankdates'),0) > 0
        DROP VIEW rankdates
go

-- Approach 1: Standard SQL
CREATE VIEW rankdates (when_taken, temperature, daterank)
AS
SELECT when_taken, temperature,
    (SELECT COUNT(DISTINCT when_taken) FROM measurements AS T1
    WHERE T1.when_taken <= T0.when_taken) AS rank
FROM measurements AS T0                                                                    
GO
SELECT * FROM rankdates ORDER BY daterank
GO

-- Correlate each value with the one right before it
DECLARE @elapse datetime
SELECT @elapse=GETDATE()
SELECT 
P1_WHEN=V1.when_taken, P2_WHEN=V2.when_taken, 
P1=V1.temperature, P2=V2.temperature,
DIFF=(V2.temperature - V1.temperature)
FROM rankdates AS V1 LEFT OUTER JOIN rankdates AS V2
ON (V2.daterank=V1.daterank + 1)
SELECT 'Elapsed Time for view'=DATEDIFF(ms, @elapse, GETDATE())
GO

-- Approach 2: Materialize rankings in temp table and then self-join
CREATE TABLE #rankdates (
when_taken datetime,
temperature numeric(4, 1),
daterank int IDENTITY PRIMARY KEY)
GO

INSERT #rankdates (when_taken, temperature)
    SELECT when_taken, temperature 
    FROM measurements
    ORDER BY when_taken ASC
GO

SELECT 
P1_WHEN=V1.when_taken, P2_WHEN=V2.when_taken, 
P1=V1.temperature, P2=V2.temperature,
DIFF=(V2.temperature - V1.temperature)
FROM #rankdates AS V1 LEFT OUTER JOIN #rankdates AS V2
ON (V2.daterank=V1.daterank + 1)
GO