Total duration "server" was in "In" state per day

Question

I have data of a server start & stop time on a daily basis for one month. I need the result containing the first start time of the day, last stop time of the day, total time the server was started in the day, for each server and each day.

Data sample:

Date & Time           Reader ServerID
3/14/2016 6:36:20 AM    IN    123
3/14/2016 6:58:45 AM    OUT   123
3/14/2016 8:06:19 AM    IN    123
3/14/2016 9:32:48 AM    OUT   123
3/15/16 6:00:00 AM      IN    123
3/15/16 6:01:00 AM      OUT   123
3/14/2016 9:46 AM       IN    124
3/14/2016 10:01 AM      OUT   124
3/14/16 11:01 AM        IN    124
3/14/16 12:01 PM        OUT   124

Expected output:

ServerID  FirstIN  Last Out  TotalInTime (min)  Date
123       6:00     09:32     86                 3/14
123       06:00    06:01     1                  3/15
124       9:46     12:01     75                 3/14

Please note that each IN will always have a corresponding OUT in the source table.

How can I solve this?

Here are the CREATE TABLE and INSERT statements:

create table tbl1 (
    serverid numeric (18,0)
    , Reader varchar (20)
    , Date_Time datetime
);
Insert into tbl1 values (123, 'In', '2015-08-24 06:00:00.000');
Insert into tbl1 values (123, 'Out', '2015-08-24 07:00:00.000');
Insert into tbl1 values (123, 'In', '2015-08-24 08:00:00.000');
Insert into tbl1 values (123, 'Out', '2015-08-24 09:00:00.000');
Insert into tbl1 values (123, 'In', '2015-08-24 10:00:00.000');
Insert into tbl1 values (123, 'Out', '2015-08-24 11:00:00.000');
Insert into tbl1 values (123, 'In', '2015-08-25 10:00:00.000');
Insert into tbl1 values (123, 'Out', '2015-08-25 11:00:00.000');
Insert into tbl1 values (124, 'In', '2015-08-24 06:15:00.000');
Insert into tbl1 values (124, 'Out', '2015-08-24 07:30:00.000');
Insert into tbl1 values (124, 'In', '2015-08-24 08:00:00.000');
Insert into tbl1 values (124, 'Out', '2015-08-24 09:30:00.000');
Insert into tbl1 values (124, 'In', '2015-08-24 10:00:00.000');
Insert into tbl1 values (124, 'Out', '2015-08-24 11:30:00.000'); 

Answer 1

Since you are certain the source data is always reliable, you might use this:

;WITH src AS
(
    SELECT t1.ServerID
        , [Date] = DATEADD(DAY, 0, DATEDIFF(DAY, 0, t1.InDate))
        , t1.InDate
        , OutDate = (
        SELECT TOP(1) OutDate = al.DateAndTime
        FROM dbo.tbl1 al
        WHERE al.Reader = 'OFF'
            AND al.ServerID = t1.ServerID
            AND al.DateAndTime > t1.InDate
        ORDER BY al.DateAndTime
        )
    FROM (
        SELECT al.ServerID
            , al.Reader
            , InDate = al.DateAndTime
        FROM dbo.tbl1 al
        WHERE al.Reader = 'ON'
        ) t1
)
, src2 AS
(
    SELECT *
        , Duration = DATEDIFF(MINUTE, src.InDate, src.OutDate)
    FROM src
)
SELECT src2.ServerID
    , src2.Date
    , FirstIn = MIN(src2.InDate)
    , LastOut = MAX(src2.OutDate)
    , Duration = SUM(src2.Duration)
FROM src2
GROUP BY src2.ServerID
    , src2.Date
ORDER BY src2.ServerID
    , src2.Date;

Output (I used the first set of sample data, since you didn't originally provide the INSERT statements):

This code collapses your source table so each row has an "in" and an "out" date, which enables us to use the DATEDIFF function to calculate the duration.

This line, [Date] = DATEADD(DAY, 0, DATEDIFF(DAY, 0, t1.InDate)) strips the time component from the InDate so we can use it to display one row per day per server. This could be moved into the table definition as a calculated, persisted column. The table definition would be:

CREATE TABLE dbo.tbl1
(
    DateAndTime DATETIME NOT NULL
    , Reader CHAR(3) NOT NULL
    , ServerID INT NOT NULL
    , DateOnly AS DATEADD(DAY, 0, DATEDIFF(DAY, 0, DateAndTime))
        PERSISTED
);

Answer 2

Row_number() / group by version. Prerequisite: "good" data, every In matches exactly one Out, every pair within the same day.

select 
  serverid, 
  dtFistIn = min(dtIn), 
  dtLastOut = max(dtOut),
  s = sum(datediff(MINUTE, dtIn, dtOut)),
  dt
from (  
  select 
    serverid, dt, pair, 
    dtIn = min(Date_Time),
    dtOut = max(Date_Time) 
  from (
    select 
      serverid, Reader, Date_Time,
      Dt = dateadd(DAY, 0, datediff(DAY, 0, Date_Time)),
      pair = row_number() over(partition by serverid, Reader order by Date_Time)
    from tbl1) t
  group by serverid, dt, pair) t2
group by serverid, dt
order by serverid, dt;

Answer 3

There is corresponding In and OUT for sure and will be in the same day

You know that the data is good now ....

;WITH Activity
AS (
    SELECT serverid
        ,IN_Time = min(Date_Time)
        ,Out_Time = max(Date_Time)
    FROM (
        SELECT serverid
            ,Date_Time
            ,
            -- pair consecutive rows.. since you know you have IN and OUT for corresponding events
            rn = (
                row_number() OVER (
                    PARTITION BY serverid ORDER BY Date_Time
                    ) - 1
                ) / 2
        FROM dbo.tbl1
        ) a
    GROUP BY serverid
        ,rn
    )
SELECT serverid
    ,convert(DATE, IN_Time) AS StDate
    ,left(convert(TIME, min(IN_Time)), 5) AS FirstIN
    ,left(convert(TIME, Max(Out_Time)), 5) AS LastOut
    ,sum(datediff(MINUTE, IN_Time, Out_Time)) AS [TotalInTime(Min)]
FROM Activity
GROUP BY serverid
    ,dateadd(day, 0, datediff(day, 0, IN_Time))
    ,convert(DATE, IN_Time)
ORDER BY serverid
    ,convert(DATE, IN_Time)

Below is your desired output

Note: Your given DDL and your expected output do not match. I have used below data.

Insert into tbl1 values (123, 'In', '2015-08-24 06:00:00.000');
Insert into tbl1 values (123, 'Out', '2015-08-24 07:00:00.000');
Insert into tbl1 values (123, 'In', '2015-08-24 08:00:00.000');
Insert into tbl1 values (123, 'Out', '2015-08-24 09:00:00.000');
Insert into tbl1 values (123, 'In', '2015-08-24 10:00:00.000');
Insert into tbl1 values (123, 'Out', '2015-08-24 11:00:00.000');
Insert into tbl1 values (123, 'In', '2015-08-25 10:00:00.000');
Insert into tbl1 values (123, 'Out', '2015-08-25 11:00:00.000');
Insert into tbl1 values (124, 'In', '2015-08-24 06:15:00.000');
Insert into tbl1 values (124, 'Out', '2015-08-24 07:30:00.000');
Insert into tbl1 values (124, 'In', '2015-08-24 08:00:00.000');
Insert into tbl1 values (124, 'Out', '2015-08-24 09:30:00.000');
Insert into tbl1 values (124, 'In', '2015-08-24 10:00:00.000');
Insert into tbl1 values (124, 'Out', '2015-08-24 11:30:00.000'); 

Answer 4

I tried an analytic function approach. (I don't have a SQL Server instance, but I've used PostgreSQL and basic window/analytic functions - it should work out fairly similarly - I'll try SQLFiddle, if I can get it to work!). It does depend (as do the other solutions) on there being an OFF for every ON.

First, I created the table:

CREATE TABLE server (inc_time timestamp, server_status varchar(3), server_id smallint);

Then populated it:

INSERT INTO server VALUES ('2016-03-14 06:36:20', 'ON',  123);
INSERT INTO server VALUES ('2016-03-14 06:58:45', 'OFF', 123);
INSERT INTO server VALUES ('2016-03-14 08:06:19', 'ON',  123);
INSERT INTO server VALUES ('2016-03-14 09:32:48', 'OFF', 123);
INSERT INTO server VALUES ('2016-03-15 06:00:00', 'ON', 123);
INSERT INTO server VALUES ('2016-03-15 06:01:00', 'OFF', 123);

INSERT INTO server VALUES ('2016-03-14 09:46:00', 'ON', 124);
INSERT INTO server VALUES ('2016-03-14 10:01:00', 'OFF', 124);
INSERT INTO server VALUES ('2016-03-14 11:01:00', 'ON', 124);
INSERT INTO server VALUES ('2016-03-14 12:01:00', 'OFF', 124);

Then, I ran this query:

; WITH cte1 AS
(
SELECT inc_time, server_status, server_id, 
LAG(inc_time) OVER (ORDER BY inc_time) val,
inc_time - LAG(inc_time) OVER (ORDER BY inc_time) AS temps
FROM server
)
SELECT 
  EXTRACT(HOUR FROM SUM(temps)) * 60 + EXTRACT(MINUTE FROM  SUM(temps)) AS minutes_up, 
  EXTRACT(DAY FROM inc_time) AS server_day, server_id 
FROM cte1
WHERE server_status = 'OFF'
GROUP BY server_day, server_id
ORDER BY server_id, server_day

And obtained the result (correct with the data used):

minutes_up;  server_day;  server_id
108;         14;          123
1;           15;          123
75;          14;          124

However, if the ON/OFF straddles midnight, the query above does not return the correct results - but the one below does.

Putting in the further data below - note the ON/OFFs here straddle midnight:

INSERT INTO server VALUES ('2016-03-19 11:01:00', 'ON', 125);
INSERT INTO server VALUES ('2016-03-20 12:01:00', 'OFF', 125);
INSERT INTO server VALUES ('2016-03-23 11:01:00', 'ON', 125);
INSERT INTO server VALUES ('2016-03-25 12:01:00', 'OFF', 125);

Using this:

; WITH cte1 AS
(
  SELECT inc_time, server_status, server_id, 
  LAG(inc_time) OVER (ORDER BY inc_time) val,
  inc_time - LAG(inc_time) OVER (ORDER BY inc_time) AS temps
  FROM server
)
SELECT 
  server_id,
  EXTRACT(DAY FROM inc_time) AS day_id,
  SUM(EXTRACT(EPOCH FROM temps)/60) AS minutes_up
  SUM(EXTRACT(EPOCH FROM temps)/60/60) AS hours_up
FROM cte1
WHERE server_status = 'OFF'
GROUP BY server_id, day_id
ORDER BY server_id, day_id;

Gives:

server_id;day_id;minutes_up;hours_up
123;14;108.9;1.815
123;15;1;0.0166666666666667
124;14;75;1.25
125;20;1500;25
125;25;2940;49

Using the EPOCH (i.e. Unix_Timestamp) will cope with this scenario. Note that LAG requires SQL Server 2012 or later.