CODEWITHSUNDEEP
sqlsql-serverjoin
Legend
Legend

Reputation: 116950

Computing difference in rows for all except consecutive days?

I have a table as follows. I want to compute the difference in dates (in seconds) between consecutive rows according to the following:

I am currently doing a self-join to handle the first case but am not sure if there is a good way to handle the second case. Any suggestion?

The following also gives an example:

CREATE TABLE #TEMP(Person VARCHAR(100), StartTime Datetime, TotalSeconds INT)


INSERT INTO #TEMP VALUES('A',   '2013-02-20',   49800); -- We want to take the difference with the next row in this case
INSERT INTO #TEMP VALUES('A',   '2013-02-25',   3000); -- Before taking the difference, I want to first merge the next four rows because 5th March is followed by three days with the value 86400
INSERT INTO #TEMP VALUES('A',   '2013-03-05',   2100); 
INSERT INTO #TEMP VALUES('A',   '2013-03-06',   86400);
INSERT INTO #TEMP VALUES('A',   '2013-03-07',   86400);
INSERT INTO #TEMP VALUES('A',   '2013-03-08',   86400);
INSERT INTO #TEMP VALUES('A',   '2013-03-09',   17100);
INSERT INTO #TEMP VALUES('B',   '2012-04-24',   22500);
INSERT INTO #TEMP VALUES('B',   '2012-04-26',   600);
INSERT INTO #TEMP VALUES('B',   '2012-04-27',   10500);
INSERT INTO #TEMP VALUES('B',   '2012-04-29',   41400);
INSERT INTO #TEMP VALUES('B',   '2012-05-04',   86100);


SELECT *
FROM #TEMP

DROP TABLE #TEMP

Upvotes: 1

Views: 95

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270583

The following handles the second case:

select Person, MIN(StartTime) as StartTime, MAX(StartTime) as maxStartTime
from (SELECT *,
             dateadd(d, - ROW_NUMBER() over (partition by person order by StartTime), StartTime) as thegroup
      FROM #TEMP t
     ) t
group by Person, thegroup

It groups all the time periods for a person, with consecutive dates collapsing into a single period (with a begin and end time). The trick is to assign a sequence number, using row_number() and then take the difference from StartTime. This difference is constant for a group of consecutive dates -- hence the outer group by.

You can use a with statement to put this into your query and then get the difference that you desire between consecutive rows.

Upvotes: 2

Related Questions