CODEWITHSUNDEEP
sql-server-2008t-sql
Briskstar Technologies
Briskstar Technologies

Reputation: 2253

Time difference for each row in group

I have table as below:

    GroupOrderID    Station           Date
-----------------------------------------------
    28797   NB003   2013-01-03 12:53:00
    28797   NB003   2013-01-03 17:12:00
    28797   NB003   2013-01-04 14:45:00
    28797   NB003   2013-01-04 15:57:00
    28797   NB003   2013-01-06 16:08:00
    28797   NB003   2013-01-07 10:28:00
    28797   NB003   2013-01-07 10:29:00
    28797   CRM220  2013-01-07 16:31:00
    28797   CRM220  2013-01-07 17:04:00
    28797   CRM220  2013-01-07 17:04:00
    28797   CRM220  2013-01-07 17:04:00
    28797   CRM220  2013-01-07 17:04:00
    28797   CRM220  2013-01-07 17:24:00
    28797   CRM220  2013-01-07 17:24:00
    28797   STEENOVE    2013-01-11 11:03:00

I want time difference between two dates in for each row in seconds., for each station wise,

But there is condition for MAX SECONDS BREAK condition. Like, During calculating seconds for Station CRM220 First and Last datetime, We will not calculate that time which have seconds > MAX SECONDS BREAK.

So we will count all seconds between first and last measurement for station, and exclude time gap of seconds mentioned in Max Break. Like Station NB003, CRM220 each have first and last date and I want difference in seconds.

Upvotes: 1

Views: 1431

Answers (1)

Hiten004
Hiten004

Reputation: 2481

This tsql will give you diff in secound.** 

with cte as
(SELECT
   ROW_NUMBER() OVER (PARTITION BY GroupOrderID    , Station ORDER BY CDate) row,
  GroupOrderID    , Station, CDate
 FROM #supportContacts)
SELECT 
   a.GroupOrderID    , a.Station ,
   DATEDIFF ( second , b.CDate , a.CDate) as 'DiffinSec'
FROM
   cte a
   LEFT  JOIN cte b
   on a.GroupOrderID = b.GroupOrderID
   and a.Station = b.Station
    and a.row = b.row+1

Results

Results

Upvotes: 1

Related Questions