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

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 (

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

StackExchange Database Administrators Q#138021, answer score: 3