【SQLレシピ】区切りの時刻を指定して、日毎の最初と最後の時刻を取得する

SQL SeverやMySQLで区切りの時刻を指定して、日毎の最初と最後の時刻を取得したい！

概要

この記事では、SQL SeverやMySQLで区切りの時刻を指定して、日毎の最初と最後の時刻を取得する手順を掲載する。

仕様書

環境

• SQL Server 15.0.2104
• MariaDB 10.3.34

手順書

例として、設備の利用履歴を管理する下記のテーブルがある。（正規化？それってうめえんか？）

列名	データ型	備考
id	int	主キー
emp_num	int	社員番号
emp_name	varchar(32)	社員の名前
facility_id	int	施設のID
checked_at	datetime	時刻
is_checked	bit	1なら利用開始、0なら利用終了

テーブルに登録されてるデータはこんな感じ。

id	emp_num	emp_name	facility_id	checked_at	is_checked
1	2	カムパネルラ	2	2024-04-01 08:00:00	1
2	1	ジョバンニ	1	2024-04-01 09:00:00	1
3	2	カムパネルラ	3	2024-04-01 10:30:00	1
4	2	カムパネルラ	3	2024-04-01 16:00:00	2
5	1	ジョバンニ	1	2024-04-01 17:00:00	2
6	2	カムパネルラ	2	2024-04-02 2:00:00	2
7	2	カムパネルラ	1	2024-04-02 5:00:00	1
8	1	ジョバンニ	2	2024-04-02 11:00:00	1
9	3	ザネリ	2	2024-04-02 14:00:00	1
10	3	ザネリ	2	2024-04-02 16:00:00	2
11	1	ジョバンニ	2	2024-04-02 20:00:00	2

まず、日毎、社員毎の最初の利用開始時刻と最後の利用週力時刻を取得する。該当の時刻がない場合はnullとする。日の区切りは3:00として集計する。

SQL Serverの場合。

WITH filtered AS (
    SELECT
        emp_num,
        emp_name,
        checked_at,
        is_checkin,
        CASE
            WHEN DATEPART(HOUR,checked_at) >= 3
            THEN CAST(CAST(checked_at AS DATE) AS DATETIME)
            ELSE DATEADD(DAY, -1, CAST(CAST(checked_at AS DATE) AS DATETIME))
        END AS checked_date
    FROM
        usage_history_of_facility
)
SELECT
    emp_num,
    emp_name,
    checked_date,
    MIN(CASE WHEN is_checkin = 1 THEN checked_at ELSE NULL END) AS first_checked_at,
    MAX(CASE WHEN is_checkin = 0 THEN checked_at ELSE NULL END) AS last_checkea_at
FROM
    filtered
GROUP BY
    emp_num,
    emp_name,
    checked_date
ORDER BY
    checked_date asc,
    emp_num asc;

MySQLの場合。

WITH filtered AS (
    SELECT
        emp_num,
        emp_name,
        checked_at,
        is_checkin,
        CASE
            WHEN HOUR(checked_at) >= 3
            THEN DATE(checked_at)
            ELSE DATE_SUB(DATE(checked_at), INTERVAL 1 DAY)
        END AS checked_date
    FROM
        usage_history_of_facility
)
SELECT
    emp_num,
    emp_name,
    checked_date,
    MIN(CASE WHEN is_checkin = 1 THEN checked_at ELSE NULL END) AS first_checked_at,
    MAX(CASE WHEN is_checkin = 0 THEN checked_at ELSE NULL END) AS last_checkea_at
FROM
    filtered
GROUP BY
    emp_num,
    emp_name,
    checked_date
ORDER BY
    checked_date ASC,
    emp_num ASC;

出力結果。

emp_num emp_name    checked_date    first_checked_at    last_checkea_at
1   ジョバンニ   2024-04-01 00:00:00.000 2024-04-01 09:00:00.000 2024-04-01 17:00:00.000
2   カムパネルラ  2024-04-01 00:00:00.000 2024-04-01 08:00:00.000 2024-04-02 02:00:00.000
1   ジョバンニ   2024-04-02 00:00:00.000 2024-04-02 11:00:00.000 2024-04-02 20:00:00.000
2   カムパネルラ  2024-04-02 00:00:00.000 2024-04-02 05:00:00.000 NULL
3   ザネリ 2024-04-02 00:00:00.000 2024-04-02 14:00:00.000 2024-04-02 16:00:00.000

出社時間と退社時間的なデータを取得できる。

次に、日毎、施設毎の最初の利用開始時刻と最後の利用週力時刻を取得する。

SQL Serverの場合。

WITH filtered AS (
    SELECT
        emp_num,
        emp_name,
        facility_id,
        checked_at,
        is_checkin,
        CASE
            WHEN DATEPART(HOUR,checked_at) >= 3
            THEN CAST(CAST(checked_at AS DATE) AS DATETIME)
            ELSE DATEADD(DAY, -1, CAST(CAST(checked_at AS DATE) AS DATETIME))
        END AS checked_date
    FROM
        usage_history_of_facility
),
first_last_check AS (
    SELECT
        facility_id,
        checked_date,
        MIN(CASE WHEN is_checkin = 1 THEN checked_at ELSE NULL END) AS first_checked_at,
        MAX(CASE WHEN is_checkin = 0 THEN checked_at ELSE NULL END) AS last_checkea_at
    FROM
        filtered
    GROUP BY
        facility_id,
        checked_date
),
first_check_details AS (
    SELECT
        f.facility_id,
        f.checked_date,
        fl.first_checked_at,
        f.emp_num AS first_check_emp_num,
        f.emp_name AS first_check_emp_name
    FROM
        filtered f
    JOIN
        first_last_check fl
    ON
        f.facility_id = fl.facility_id AND f.checked_at = fl.first_checked_at
),
last_check_details AS (
    SELECT
        f.facility_id,
        f.checked_date,
        fl.last_checkea_at,
        f.emp_num AS last_check_emp_num,
        f.emp_name AS last_check_emp_name
    FROM
        filtered f
    JOIN
        first_last_check fl
    ON
        f.facility_id = fl.facility_id AND f.checked_at = fl.last_checkea_at
)
SELECT
    fl.checked_date,
    fl.facility_id,
    fl.first_checked_at,
    fcd.first_check_emp_num,
    fcd.first_check_emp_name,
    fl.last_checkea_at,
    lcd.last_check_emp_num,
    lcd.last_check_emp_name
FROM
    first_last_check fl
JOIN
    first_check_details fcd
ON
    fl.facility_id = fcd.facility_id AND fl.checked_date = fcd.checked_date
JOIN
    last_check_details lcd
ON
    fl.facility_id = lcd.facility_id AND fl.checked_date = lcd.checked_date
ORDER BY
    fl.checked_date ASC,
    fl.facility_id ASC;

MySQLの場合。

WITH filtered AS (
    SELECT
        emp_num,
        emp_name,
        checked_at,
        is_checkin,
        CASE
            WHEN HOUR(checked_at) >= 3
            THEN DATE(checked_at)
            ELSE DATE_SUB(DATE(checked_at), INTERVAL 1 DAY)
        END AS checked_date
    FROM
        usage_history_of_facility
)
SELECT
    emp_num,
    emp_name,
    checked_date,
    MIN(CASE WHEN is_checkin = 1 THEN checked_at ELSE NULL END) AS first_checked_at,
    MAX(CASE WHEN is_checkin = 0 THEN checked_at ELSE NULL END) AS last_checkea_at
FROM
    filtered
GROUP BY
    emp_num,
    emp_name,
    checked_date
ORDER BY
    checked_date ASC,
    emp_num ASC;

出力結果。

2024-04-01 00:00:00.000 1   2024-04-01 09:00:00.000 1   ジョバンニ   2024-04-01 17:00:00.000 1   ジョバンニ
2024-04-01 00:00:00.000 2   2024-04-01 08:00:00.000 2   カムパネルラ  2024-04-02 02:00:00.000 2   カムパネルラ
2024-04-01 00:00:00.000 3   2024-04-01 10:30:00.000 2   カムパネルラ  2024-04-01 16:00:00.000 2   カムパネルラ
2024-04-02 00:00:00.000 2   2024-04-02 11:00:00.000 1   ジョバンニ   2024-04-02 20:00:00.000 1   ジョバンニ

施設の利用管理的なデータを取得できる。

まとめ（感想文）

勤怠管理や施設管理に使えるかもね！

退社してないカムパネルラの健康状態が気になる。