Intervals Between Start and End Times

Question

I have been tasked with creating a report that estimates the physical occupancy of a building based on a system login and logout time. For example if employee 1 logs in at 0730 and logs out at 1600, I need to tally that one employee was present for each half hour interval in between. I know its not the proper use of SO but I am really just seeking a best practices on how to accomplish this. The dataset that I am using for this is rather large since they are seeking a daily value for the past year. My first thought would be to create a stored procedure or a UDF to accomplish this but would much rather use straight SQL for performance reasons.

For example given the sample data below

<table>
<thead><tr><th>EmployeeID</th><th>Date</th><th>Login</th><th>Logout</th></tr></thead><tbody>
 <tr><td>123</td><td>10/31/2018</td><td>10/31/2018 8:30 AM</td><td>10/31/2018 10:35 AM</td></tr>
 <tr><td>234</td><td>10/31/2018</td><td>10/31/2018 9:30 AM</td><td>10/31/2018 11:37 AM</td></tr>
 <tr><td>345</td><td>10/31/2018</td><td>10/31/2018 9:00 AM</td><td>10/31/2018 11:09 AM</td></tr>
</tbody></table>

I am looking to get the results in this format

<table>
<thead><tr><th>DATE</th><th>8:30:00 AM</th><th>9:00:00 AM</th><th>9:30:00 AM</th><th>10:00:00 AM</th><th>10:30:00 AM</th><th>11:00:00 AM</th><th>11:30:00 AM</th></tr></thead><tbody>
 <tr><td>10/31/2018</td><td>1</td><td>2</td><td>3</td><td>3</td><td>3</td><td>2</td><td>1</td></tr>
</tbody></table>

Could you provide some sample data and expect result? that really help — D-Shih, Oct 31 '18 at 13:03
I see the possibility for two different questions here. (A) How to match separate event rows for login and logout to determine intervals (Like [this](https://stackoverflow.com/questions/11693939/calculate-time-between-on-and-off-status-rows-sql-server).) and (2) how to change arbitrary time intervals into sets of half-hour blocks. You might be asking either, both or something else entirely. Please read [this](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) for some tips on improving your question. — HABO, Oct 31 '18 at 13:14
Sample data is best served as [DDL](https://en.wikipedia.org/wiki/Data_definition_language) + [DML](https://en.wikipedia.org/wiki/Data_manipulation_language). Please [edit] your question to include it, your current attempt and your desired results. For more details, [read this.](https://dba.meta.stackexchange.com/questions/2976/help-me-write-this-query-in-sql) — Zohar Peled, Oct 31 '18 at 13:21
Does everyone get thrown out before midnight or could someone start at 10:00PM and work until 6:00AM the following morning? — HABO, Oct 31 '18 at 14:01

D-Shih · Accepted Answer · 2018-10-31T14:11:13.090

You can try to use CTE recursive with condition aggregate function.

CTE recursive makes calendar table for each timetable. then use COUNT with CASE WHEN to the time amount.

;WITH CTE AS (
   SELECT [Date],Login,Logout 
   FROM T
   UNION ALL
   SELECT [Date],DATEADD(mi, 30, Login) ,Logout
   FROM CTE 
   WHERE  DATEADD(mi, 30, Login) < Logout
),CelanderCte AS(
    SELECT *, CAST(Login AS TIME) LoginTitme
    FROM CTE
)


SELECT convert(char(10), [Date], 101) 'DATE',
    COUNT(CASE WHEN LoginTitme >= '8:30' AND LoginTitme < '9:00'    THEN 1 END) '8:30:00 AM',
    COUNT(CASE WHEN LoginTitme >= '9:00' AND LoginTitme < '9:30'    THEN 1 END) '9:00:00 AM',
    COUNT(CASE WHEN LoginTitme >= '9:30' AND LoginTitme < '10:00'   THEN 1 END) '9:30:00 AM',
    COUNT(CASE WHEN LoginTitme >= '10:00' AND LoginTitme < '10:30'  THEN 1 END) '10:00:00 AM',
    COUNT(CASE WHEN LoginTitme >= '10:30' AND  LoginTitme <'11:00'  THEN 1 END) '10:30:00 AM',
        COUNT(CASE WHEN LoginTitme >= '11:00' AND  LoginTitme <'11:30'  THEN 1 END) '11:00:00 AM'
FROM CelanderCte
GROUP BY CONVERT(char(10), [Date],101)

sqlfiddle

Result

DATE          8:30:00 AM    9:00:00 AM  9:30:00 AM  10:00:00 AM 10:30:00 AM 11:00:00 AM
10/31/2018    1             2           3           3           3           2

Aside: Moving the casts into the CTE will clean up the conditional aggregation a bit. — HABO, Oct 31 '18 at 14:02

score 1 · Answer 2 · answered Oct 31 '18 at 16:30

Since I already had it written out...

/* ===================================================================
Start by creating some test data....
=================================================================== */

IF OBJECT_ID('tempdb..#LogTimeInfo', 'U') IS NULL 
BEGIN   -- DROP TABLE #LogTimeInfo;
    CREATE TABLE #LogTimeInfo (
        EmployeeID INT NOT NULL,
        LoginDT DATETIME NOT NULL,
        LogoutDT DATETIME NOT NULL,
        PRIMARY KEY CLUSTERED (LoginDT, EmployeeID) 
        WITH (IGNORE_DUP_KEY = ON)
        );

    WITH 
        cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)), 
        cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),
        cte_n3 (n) AS (SELECT 1 FROM cte_n2 a CROSS JOIN cte_n2 b),
        cte_Tally (n) AS (
            SELECT TOP (1000000)
                ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
            FROM
                cte_n3 a CROSS JOIN cte_n3 b
            )
    INSERT #LogTimeInfo (EmployeeID, LoginDT, LogoutDT) 
    SELECT 
        ROW_NUMBER() OVER (PARTITION BY rd.rand_day ORDER BY t.n),
        rit.rand_in_time,
        rot.rand_out_time
    FROM
        cte_Tally t
        CROSS APPLY ( VALUES (DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) ) td (today)
        CROSS APPLY ( VALUES (DATEADD(DAY, -1 * ABS(CHECKSUM(NEWID())) % 500, td.today)) ) rd (rand_day)                -- 500 day range
        CROSS APPLY ( VALUES (DATEADD(MINUTE, ABS(CHECKSUM(NEWID())) % 301 + 300, rd.rand_day)) ) rit (rand_in_time)    -- between 5:00 am & 10: am
        CROSS APPLY ( VALUES (DATEADD(MINUTE, ABS(CHECKSUM(NEWID())) % 301 + 300, rit.rand_in_time)) ) rot (rand_out_time); -- betweem 5 & 10 hours after clockin
END;


/* ===================================================================
The actual solution....
=================================================================== */

DECLARE @today DATETIME = CONVERT(DATE, GETDATE());

WITH    -- Use the cte to create a virtual calendar table that has all dates within the desired date range.
    cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)), 
    cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),
    cte_Dates (d) AS (
        SELECT TOP (365)
            DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 366, @today)
        FROM
            cte_n2 a CROSS JOIN cte_n2 b
        )
SELECT 
    d.d,
    [00:00] = COUNT(CASE WHEN lti.LoginDT < DATEADD(MINUTE, 30 , d.d) AND lti.LogoutDT > DATEADD(MINUTE, 0, d.d) THEN 1 END),
    [00:30] = COUNT(CASE WHEN lti.LoginDT < DATEADD(MINUTE, 60 , d.d) AND lti.LogoutDT > DATEADD(MINUTE, 30, d.d) THEN 1 END),
    [01:00] = COUNT(CASE WHEN lti.LoginDT < DATEADD(MINUTE, 90 , d.d) AND lti.LogoutDT > DATEADD(MINUTE, 60, d.d) THEN 1 END),
    [01:30] = COUNT(CASE WHEN lti.LoginDT < DATEADD(MINUTE, 120 , d.d) AND lti.LogoutDT > DATEADD(MINUTE, 90, d.d) THEN 1 END),
    [02:00] = COUNT(CASE WHEN lti.LoginDT < DATEADD(MINUTE, 150 , d.d) AND lti.LogoutDT > DATEADD(MINUTE, 120, d.d) THEN 1 END),
    [02:30] = COUNT(CASE WHEN lti.LoginDT < DATEADD(MINUTE, 180 , d.d) AND lti.LogoutDT > DATEADD(MINUTE, 150, d.d) THEN 1 END),
    [03:00] = COUNT(CASE WHEN lti.LoginDT < DATEADD(MINUTE, 210 , d.d) AND lti.LogoutDT > DATEADD(MINUTE, 180, d.d) THEN 1 END),
    [03:30] = COUNT(CASE WHEN lti.LoginDT < DATEADD(MINUTE, 240 , d.d) AND lti.LogoutDT > DATEADD(MINUTE, 210, d.d) THEN 1 END),
    [04:00] = COUNT(CASE WHEN lti.LoginDT < DATEADD(MINUTE, 270 , d.d) AND lti.LogoutDT > DATEADD(MINUTE, 240, d.d) THEN 1 END),
    [04:30] = COUNT(CASE WHEN lti.LoginDT < DATEADD(MINUTE, 300 , d.d) AND lti.LogoutDT > DATEADD(MINUTE, 270, d.d) THEN 1 END),
    [05:00] = COUNT(CASE WHEN lti.LoginDT < DATEADD(MINUTE, 330 , d.d) AND lti.LogoutDT > DATEADD(MINUTE, 300, d.d) THEN 1 END),
    [05:30] = COUNT(CASE WHEN lti.LoginDT < DATEADD(MINUTE, 360 , d.d) AND lti.LogoutDT > DATEADD(MINUTE, 330, d.d) THEN 1 END),
    [06:00] = COUNT(CASE WHEN lti.LoginDT < DATEADD(MINUTE, 390 , d.d) AND lti.LogoutDT > DATEADD(MINUTE, 360, d.d) THEN 1 END),
    [06:30] = COUNT(CASE WHEN lti.LoginDT < DATEADD(MINUTE, 420 , d.d) AND lti.LogoutDT > DATEADD(MINUTE, 390, d.d) THEN 1 END),
    [07:00] = COUNT(CASE WHEN lti.LoginDT < DATEADD(MINUTE, 450 , d.d) AND lti.LogoutDT > DATEADD(MINUTE, 420, d.d) THEN 1 END),
    [07:30] = COUNT(CASE WHEN lti.LoginDT < DATEADD(MINUTE, 480 , d.d) AND lti.LogoutDT > DATEADD(MINUTE, 450, d.d) THEN 1 END),
    [08:00] = COUNT(CASE WHEN lti.LoginDT < DATEADD(MINUTE, 510 , d.d) AND lti.LogoutDT > DATEADD(MINUTE, 480, d.d) THEN 1 END),
    [08:30] = COUNT(CASE WHEN lti.LoginDT < DATEADD(MINUTE, 540 , d.d) AND lti.LogoutDT > DATEADD(MINUTE, 510, d.d) THEN 1 END),
    [09:00] = COUNT(CASE WHEN lti.LoginDT < DATEADD(MINUTE, 570 , d.d) AND lti.LogoutDT > DATEADD(MINUTE, 540, d.d) THEN 1 END),
    [09:30] = COUNT(CASE WHEN lti.LoginDT < DATEADD(MINUTE, 600 , d.d) AND lti.LogoutDT > DATEADD(MINUTE, 570, d.d) THEN 1 END),
    [10:00] = COUNT(CASE WHEN lti.LoginDT < DATEADD(MINUTE, 630 , d.d) AND lti.LogoutDT > DATEADD(MINUTE, 600, d.d) THEN 1 END),
    [10:30] = COUNT(CASE WHEN lti.LoginDT < DATEADD(MINUTE, 660 , d.d) AND lti.LogoutDT > DATEADD(MINUTE, 630, d.d) THEN 1 END),
    [11:00] = COUNT(CASE WHEN lti.LoginDT < DATEADD(MINUTE, 690 , d.d) AND lti.LogoutDT > DATEADD(MINUTE, 660, d.d) THEN 1 END),
    [11:30] = COUNT(CASE WHEN lti.LoginDT < DATEADD(MINUTE, 720 , d.d) AND lti.LogoutDT > DATEADD(MINUTE, 690, d.d) THEN 1 END),
    [12:00] = COUNT(CASE WHEN lti.LoginDT < DATEADD(MINUTE, 750 , d.d) AND lti.LogoutDT > DATEADD(MINUTE, 720, d.d) THEN 1 END),
    [12:30] = COUNT(CASE WHEN lti.LoginDT < DATEADD(MINUTE, 780 , d.d) AND lti.LogoutDT > DATEADD(MINUTE, 750, d.d) THEN 1 END),
    [13:00] = COUNT(CASE WHEN lti.LoginDT < DATEADD(MINUTE, 810 , d.d) AND lti.LogoutDT > DATEADD(MINUTE, 780, d.d) THEN 1 END),
    [13:30] = COUNT(CASE WHEN lti.LoginDT < DATEADD(MINUTE, 840 , d.d) AND lti.LogoutDT > DATEADD(MINUTE, 810, d.d) THEN 1 END),
    [14:00] = COUNT(CASE WHEN lti.LoginDT < DATEADD(MINUTE, 870 , d.d) AND lti.LogoutDT > DATEADD(MINUTE, 840, d.d) THEN 1 END),
    [14:30] = COUNT(CASE WHEN lti.LoginDT < DATEADD(MINUTE, 900 , d.d) AND lti.LogoutDT > DATEADD(MINUTE, 870, d.d) THEN 1 END),
    [15:00] = COUNT(CASE WHEN lti.LoginDT < DATEADD(MINUTE, 930 , d.d) AND lti.LogoutDT > DATEADD(MINUTE, 900, d.d) THEN 1 END),
    [15:30] = COUNT(CASE WHEN lti.LoginDT < DATEADD(MINUTE, 960 , d.d) AND lti.LogoutDT > DATEADD(MINUTE, 930, d.d) THEN 1 END),
    [16:00] = COUNT(CASE WHEN lti.LoginDT < DATEADD(MINUTE, 990 , d.d) AND lti.LogoutDT > DATEADD(MINUTE, 960, d.d) THEN 1 END),
    [16:30] = COUNT(CASE WHEN lti.LoginDT < DATEADD(MINUTE, 1020 , d.d) AND lti.LogoutDT > DATEADD(MINUTE, 990, d.d) THEN 1 END),
    [17:00] = COUNT(CASE WHEN lti.LoginDT < DATEADD(MINUTE, 1050 , d.d) AND lti.LogoutDT > DATEADD(MINUTE, 1020, d.d) THEN 1 END),
    [17:30] = COUNT(CASE WHEN lti.LoginDT < DATEADD(MINUTE, 1080 , d.d) AND lti.LogoutDT > DATEADD(MINUTE, 1050, d.d) THEN 1 END),
    [18:00] = COUNT(CASE WHEN lti.LoginDT < DATEADD(MINUTE, 1110 , d.d) AND lti.LogoutDT > DATEADD(MINUTE, 1080, d.d) THEN 1 END),
    [18:30] = COUNT(CASE WHEN lti.LoginDT < DATEADD(MINUTE, 1140 , d.d) AND lti.LogoutDT > DATEADD(MINUTE, 1110, d.d) THEN 1 END),
    [19:00] = COUNT(CASE WHEN lti.LoginDT < DATEADD(MINUTE, 1170 , d.d) AND lti.LogoutDT > DATEADD(MINUTE, 1140, d.d) THEN 1 END),
    [19:30] = COUNT(CASE WHEN lti.LoginDT < DATEADD(MINUTE, 1200 , d.d) AND lti.LogoutDT > DATEADD(MINUTE, 1170, d.d) THEN 1 END),
    [20:00] = COUNT(CASE WHEN lti.LoginDT < DATEADD(MINUTE, 1230 , d.d) AND lti.LogoutDT > DATEADD(MINUTE, 1200, d.d) THEN 1 END),
    [20:30] = COUNT(CASE WHEN lti.LoginDT < DATEADD(MINUTE, 1260 , d.d) AND lti.LogoutDT > DATEADD(MINUTE, 1230, d.d) THEN 1 END),
    [21:00] = COUNT(CASE WHEN lti.LoginDT < DATEADD(MINUTE, 1290 , d.d) AND lti.LogoutDT > DATEADD(MINUTE, 1260, d.d) THEN 1 END),
    [21:30] = COUNT(CASE WHEN lti.LoginDT < DATEADD(MINUTE, 1320 , d.d) AND lti.LogoutDT > DATEADD(MINUTE, 1290, d.d) THEN 1 END),
    [22:00] = COUNT(CASE WHEN lti.LoginDT < DATEADD(MINUTE, 1350 , d.d) AND lti.LogoutDT > DATEADD(MINUTE, 1320, d.d) THEN 1 END),
    [22:30] = COUNT(CASE WHEN lti.LoginDT < DATEADD(MINUTE, 1380 , d.d) AND lti.LogoutDT > DATEADD(MINUTE, 1350, d.d) THEN 1 END),
    [23:00] = COUNT(CASE WHEN lti.LoginDT < DATEADD(MINUTE, 1410 , d.d) AND lti.LogoutDT > DATEADD(MINUTE, 1380, d.d) THEN 1 END),
    [23:30] = COUNT(CASE WHEN lti.LoginDT < DATEADD(MINUTE, 1440 , d.d) AND lti.LogoutDT > DATEADD(MINUTE, 1410, d.d) THEN 1 END)
FROM
    cte_Dates d
    LEFT JOIN #LogTimeInfo lti
        ON d.d <= lti.LoginDT
        AND DATEADD(DAY, 1, d.d) > lti.LoginDT
GROUP BY 
    d.d;

Intervals Between Start and End Times

2 Answers2