Q:

calculate downtime based on status values

Im trying to calculate downtime for service from data present in a table. (sql database)
table will contain data as follows:
service       Status       RecordedTime
   ID           ID

   1             2      2015-07-08 12:02:25.257
   1             3      2015-07-08 12:07:25.257
   1             0      2015-07-08 12:12:25.257
   1             4      2015-07-08 12:17:25.257
   1             3      2015-07-08 12:22:25.257
   2             0      2015-07-08 12:27:25.257
   2             2      2015-07-08 12:32:25.257
   2             4      2015-07-08 12:37:25.257
   1             0      2015-07-08 12:42:25.257
   1             3      2015-07-08 12:47:25.257
   2             0      2015-07-08 12:52:25.257
   2             1      2015-07-08 12:57:25.257
   2             2      2015-07-08 13:02:25.257
   2             3      2015-07-08 13:07:25.257

Multiple services exists , and their status are tracked , where 0,2 => down, 1,3,4 => up
I need to calculate the down time of specific service for a specific day/month/year
eg: based on the above table service "1" has the following data:
    service   Status       RecordedTime
    ID          ID
    1           0       2015-07-08 12:12:25.257
    1           4       2015-07-08 12:17:25.257
    1           0       2015-07-08 12:42:25.257
    1           3       2015-07-08 12:47:25.257

so the result for 1day would be
    Date          Service     downtime
           ID
    2015-07-08      1           10mins
    2015-07-08      2           1 hr
    ...
    2015-07-09      2           10mins
    2015-07-09      1           1 hr

Any ideas on how a query could be put together to achieve this?
i have the following query which makes them as common   
select SIPTrunkID,0 as TrunkStatus,RecordTimeUtc 
from VoipGatewaySIPTrunkStats 
where ServiceProviderSIPTrunkStatus in (0,2)     
AND RecordTimeUtc BETWEEN (GetUTCDate() -1) AND GetUTCDate() 
AND SIPTrunkId = 1 

union 

(select SIPTrunkID,1 as TrunkStatus,RecordTimeUtc 
from VoipGatewaySIPTrunkStats 
where ServiceProviderSIPTrunkStatus not in (0,2) 
AND RecordTimeUtc BETWEEN (GetUTCDate() -1) AND GetUTCDate() AND SIPTrunkId = 1)
order by RecordTimeUtc

this i have done it for single trunk and yet to get the downtime values Once done it needs to be tuned further for all trunk each day

A:

Try this query, it uses cross apply to access the next uptime event for each down time event (except for consecutive down time events, where it counts from the earliest), and computes the difference.
select 
    date, 
    serviceid, 
    sum(datediff(minute, starttime, endtime )) downtime 
from (
    select 
       cast(t1.recordedtime as date) as date, 
       t1.serviceid, 
       min(t1.recordedtime) starttime, 
       ca.recordedtime endtime
    from your_table t1
    cross apply (
       select top 1 recordedtime
       from your_table
       where serviceid = t1.serviceid 
       and recordedtime > t1.recordedtime 
       and statusid in (1,3,4) 
       order by recordedtime
    ) ca
    where t1.statusid in (0,2)
    group by cast(t1.recordedtime as date), t1.serviceid, ca.RecordedTime
) a
group by date, serviceId;

With your sample data the result would be:
date        serviceid   downtime
2015-07-08  1           15
2015-07-08  2           20

which I believe is correct even though it does not match your sample result (but that doesn't seem to have any relation to the actual data).
Sample SQL Fiddle

