Интервалы между временем начала и окончания

Мне было поручено создать отчет, в котором оценивается физическая занятость здания на основе времени входа в систему и времени выхода из системы. Например, если сотрудник 1 входит в систему в 07:30 и выходит из системы в 1600, мне нужно подсчитать, что один сотрудник присутствовал в течение каждого получасового интервала между ними. Я знаю, что это неправильное использование SO, но на самом деле я просто ищу лучшие практики, как это сделать. Набор данных, который я использую для этого, довольно велик, поскольку они ищут ежедневное значение за последний год. Моей первой мыслью было бы создать хранимую процедуру или UDF для достижения этой цели, но я бы предпочел использовать простой SQL по соображениям производительности.

Например, учитывая пример данных ниже

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

Я ищу результаты в этом формате

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

Не могли бы вы предоставить образцы данных и ожидать результата? это действительно поможет

D-Shih 31.10.2018 14:03

Я вижу здесь возможность задать два разных вопроса. (A) Как сопоставить отдельные строки событий для входа и выхода, чтобы определить интервалы (например, это.) И (2) как изменить произвольные временные интервалы в наборы получасовых блоков. Вы можете спросить либо то, и другое, либо совсем другое. Прочтите это, чтобы узнать, как улучшить свой вопрос.

HABO 31.10.2018 14:14

Как вы собираетесь отслеживать их получасовую посещаемость?

JonTout 31.10.2018 14:16

Образцы данных лучше всего использовать как DDL + DML. Пожалуйста, редактировать ваш вопрос, чтобы включить его, вашу текущую попытку и желаемые результаты. Подробнее: прочитай это.

Zohar Peled 31.10.2018 14:21

Всех выгоняют до полуночи или кто-то может начать в 22:00 и работать до 6:00 следующего утра?

HABO 31.10.2018 15:01

Часы работы этого сайта не пересекают полночь.

Steve Salowitz 31.10.2018 15:02
0
6
93
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Ответ принят как подходящий

Вы можете попробовать использовать рекурсивный CTE с функцией агрегирования условий.

CTE рекурсивно делает календарную таблицу для каждого расписания. затем используйте COUNT с CASE WHEN на время.

;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

Результат

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

В сторону: перемещение приведений в CTE немного очистит условную агрегацию.

HABO 31.10.2018 15:02

Так как это у меня уже было выписано ...

/* ===================================================================
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;

Другие вопросы по теме