sql server - Need Sql query to get time difference based on status -

below sample data

i need total time when ebstatus , dgstatus on , till next dgstatus off.

for example if consider below data:

ebstatus  | dgstatus | readtime   on  |  on  | 16/07/2017  3:00:00     on  | on   | 16/07/2017  4:00:00     on  | off  | 16/07/2017  5:00:00   on  | off  | 16/07/2017  6:00:00   on  | on   | 16/07/2017  7:00:00   on  | off  | 16/07/2017  9:00:00 

here total on time 2 hours (3 4 - > 1 hour , still on until 5, total on time 2 hours) , both status again on @ 7 , dgstatus off @ 9,so here total on time 2hours.

finally total on time 4 hours

can give query sql server 2012?

if reduce 1 row per on , off, aggregation solve problem. can using difference of row numbers method:

select ebstatus, dgstatus,        min(readtime) min_rt, max(readtime) max_rt (select t.*,              row_number() on (partition ebstatus order readtime) seqnum_d,              row_number() on (partition ebstatus, dgstatus partition readtime) seqnum_sd       t      ) t group ebstatus, dgstatus, (seqnum_sd - seqnum_d); 

from this, can totals:

select sum(datediff(hour, min_rt, next_min_rt)) on_hours (select ebstatus, dgstatus,              min(readtime) min_rt, max(readtime) max_rt,              lead(min(readtime)) on (partition ebstatus, dgstatus order min(readtime)) next_min_rt       (select t.*,                    row_number() on (partition ebstatus order readtime) seqnum_d,                    row_number() on (partition ebstatus, dgstatus partition readtime) seqnum_sd             t            ) t       group ebstatus, dgstatus, (seqnum_sd - seqnum_d)      ) t dgstatus = 'on' , ebstatus = 'on';