Sql-server – Total duration “server” was in “In” state per day

sql serversql-server-2008-r2

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');

Best Answer

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):

enter image description here

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
);
Related Question