sql - Computing difference in rows for all except consecutive days? -

i have table follows. want compute difference in dates (in seconds) between consecutive rows according following:

• if dates differ more day, go ahead , compute difference
• if dates differ more day , there consecutive days value 84600 second date, want first combine dates before taking difference

i doing self-join handle first case not sure if there way handle second case. suggestion?

the following gives example:

create table #temp(person varchar(100), starttime datetime, totalseconds int)   insert #temp values('a',   '2013-02-20',   49800); -- want take difference next row in case insert #temp values('a',   '2013-02-25',   3000); -- before taking difference, want first merge next 4 rows because 5th march followed 3 days value 86400 insert #temp values('a',   '2013-03-05',   2100);  insert #temp values('a',   '2013-03-06',   86400); insert #temp values('a',   '2013-03-07',   86400); insert #temp values('a',   '2013-03-08',   86400); insert #temp values('a',   '2013-03-09',   17100); insert #temp values('b',   '2012-04-24',   22500); insert #temp values('b',   '2012-04-26',   600); insert #temp values('b',   '2012-04-27',   10500); insert #temp values('b',   '2012-04-29',   41400); insert #temp values('b',   '2012-05-04',   86100);   select * #temp  drop table #temp 

the following handles second case:

select person, min(starttime) starttime, max(starttime) maxstarttime (select *,              dateadd(d, - row_number() on (partition person order starttime), starttime) thegroup       #temp t      ) t group person, thegroup 

it groups time periods person, consecutive dates collapsing single period (with begin , end time). trick assign sequence number, using row_number() , take difference starttime. difference constant group of consecutive dates -- hence outer group by.

you can use with statement put query , difference desire between consecutive rows.