この表とサンプルデータがあります。1か月全体または特定の日付の出席と、彼が働いていた時間や欠席した日などの情報を取得したいと思います。
CREATE TABLE Attendance
(
[EmpCode] int,
[TimeIn] datetime,
[TimeOut] datetime
)
INSERT INTO Attendance VALUES (12, '2018-08-01 09:00:00', '2018-08-01 17:36:00');
INSERT INTO Attendance VALUES (12, '2018-08-02 09:00:00', '2018-08-02 18:10:00');
INSERT INTO Attendance VALUES (12, '2018-08-03 09:25:00', '2018-08-03 16:56:00');
INSERT INTO Attendance VALUES (12, '2018-08-04 09:13:00', '2018-08-05 18:09:00');
INSERT INTO Attendance VALUES (12, '2018-08-06 09:00:00', '2018-08-07 18:15:00');
INSERT INTO Attendance VALUES (12, '2018-08-07 09:27:00', '2018-08-08 17:36:00');
INSERT INTO Attendance VALUES (12, '2018-08-08 09:35:00', '2018-08-09 17:21:00');
INSERT INTO Attendance VALUES (12, '2018-08-10 09:00:00', '2018-08-10 17:45:00');
INSERT INTO Attendance VALUES (12, '2018-08-11 09:50:00', '2018-08-11 17:31:00');
INSERT INTO Attendance VALUES (12, '2018-08-13 09:23:00', '2018-08-13 17:19:00');
INSERT INTO Attendance VALUES (12, '2018-08-15 09:21:00', '2018-08-15 17:36:00');
INSERT INTO Attendance VALUES (12, '2018-08-16 09:00:00', '2018-08-16 17:09:00');
INSERT INTO Attendance VALUES (12, '2018-08-17 09:34:00', '2018-08-17 17:29:00');
INSERT INTO Attendance VALUES (12, '2018-08-18 09:00:00', '2018-08-18 17:10:00');
INSERT INTO Attendance VALUES (12, '2018-08-20 09:34:00', '2018-08-20 17:12:00');
INSERT INTO Attendance VALUES (12, '2018-08-21 09:20:00', '2018-08-21 17:15:00');
INSERT INTO Attendance VALUES (12, '2018-08-22 09:12:00', '2018-08-22 17:19:00');
INSERT INTO Attendance VALUES (12, '2018-08-23 09:05:00', '2018-08-23 17:21:00');
INSERT INTO Attendance VALUES (12, '2018-08-24 09:07:00', '2018-08-24 17:09:00');
INSERT INTO Attendance VALUES (12, '2018-08-25 09:12:00', '2018-08-25 17:05:00');
INSERT INTO Attendance VALUES (12, '2018-08-27 09:21:00', '2018-08-27 17:46:00');
INSERT INTO Attendance VALUES (12, '2018-08-28 09:17:00', '2018-08-28 17:12:00');
INSERT INTO Attendance VALUES (12, '2018-08-29 09:00:00', '2018-08-29 17:36:00');
INSERT INTO Attendance VALUES (12, '2018-08-30 09:12:00', '2018-08-30 17:24:00');
従業員が何時間働いたかを示すクエリがありますが、データがテーブルに存在した日のみが表示されています。指定された日付の間のすべての日付を表示したいのですが、データがない場合は列にNULLを入力する必要があります。
クエリは次のとおりです。
SELECT
[EmpCode],
FirstIN = CAST(MIN([TimeIn]) AS TIME),
LastOUT = CAST(MAX([TimeOut]) AS TIME),
CONVERT(VARCHAR(6), Datediff(second, CAST(MIN([TimeIn]) AS TIME), CAST(MAX([TimeOut]) AS TIME))/3600)
+ ':'
+ RIGHT('0' + CONVERT(VARCHAR(2), (Datediff(second, CAST(MIN([TimeIn]) AS TIME), CAST(MAX([TimeOut]) AS TIME)) % 3600) / 60), 2)
+ ':'
+ RIGHT('0' + CONVERT(VARCHAR(2), Datediff(second, CAST(MIN([TimeIn]) AS TIME), CAST(MAX([TimeOut]) AS TIME)) % 60) , 2 ) AS HoursSpent,
CAST(COALESCE(TimeIn, TimeOut) AS DATE) [Date]
FROM Attendance
WHERE CAST(COALESCE(TimeIn, TimeOut) AS DATE) BETWEEN '2018-08-01' AND '2018-08-25'
GROUP BY EmpCode, TimeIn, TimeOut
そのためには、再帰的な方法を使用して可能な日付を生成する必要があります。
with t as (
select '2018-08-01' as startdt
union all
select dateadd(day, 1, startdt)
from t
where startdt < '2018-08-25'
)
select . . .
from t left join
Attendance at
on cast(coalesce(at.TimeIn, at.TimeOut) as date) = t.startdt;
ステートメントt
でAttendance
テーブルの代わりに日付を使用してSELECT
ください。
注:日付期間の数が多い場合は、クエリヒントを使用することを忘れないでください。OPTION (MAXRECURSION 0)
デフォルトでは、100
再帰レベルがあります。
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加