我有多个存储记录与用户的打卡记录。我想为每个商店创建一个报告，哪两个小时有多少员工在工作?
Clock In ID Last Name First Name In time Out time
912 Bedolla Jorge 1/1/2021 7:29 1/1/2021 11:31
912 Romero Gabriel 1/1/2021 10:55 1/1/2021 14:07
912 Bedolla Jorge 1/1/2021 12:00 1/1/2021 16:07
912 Zaragoza Daniel 1/1/2021 13:06 1/1/2021 14:57
912 Thaxton Christopher 1/1/2021 14:01 1/1/2021 16:57
912 Jones Elena 1/1/2021 14:01 1/1/2021 16:35
912 Zaragoza Daniel 1/1/2021 15:12 1/1/2021 17:09
912 Jones Elena 1/1/2021 16:45 1/1/2021 18:05
912 Smith Kirsten 1/1/2021 17:30 1/1/2021 20:01
912 Zaragoza Daniel 1/1/2021 17:41 1/1/2021 21:49
寻找类似下面的结果。(以下结果数据不正确)
store ForDate 0-2 2-4 4-6 6-8 8-10 10-12 12-14 14-16 16-18 18-20 20-22 22-0
912 2021-01-01 0 0 0 1 0 1 2 3 3 2 3 0
912 2021-01-02 0 0 2 1 2 3 2 4 2 3 3 0
912 2021-01-03 0 0 1 1 2 2 2 2 3 0 2 0
912 2021-01-04 0 0 2 0 2 1 2 2 3 3 1 0
912 2021-01-05 0 0 2 1 1 3 4 4 2 2 1 0
912 2021-01-06 0 0 2 0 2 1 2 3 3 2 3 0
912 2021-01-07 0 0 2 1 2 1 3 4 2 2 0 0
912 2021-01-08 0 0 2 2 2 1 3 2 1 2 1 0
912 2021-01-09 0 0 1 1 0 3 1 3 2 2 3 0
912 2021-01-10 0 0 2 2 1 2 2 1 1 2 2 0
我试图用下面的查询解决，但它是错误的，仍然只是inTime，但outTime正在等待。
SELECT TOP 10 store, ForDate,
ISNULL([0], 0) + ISNULL([1], 0) AS [0-1],
ISNULL([2], 0) + ISNULL([3], 0) AS [2-3],
ISNULL([4], 0) + ISNULL([5], 0) AS [4-5],
ISNULL([6], 0) + ISNULL([7], 0) AS [6-7],
ISNULL([8], 0) + ISNULL([9], 0) AS [8-9],
ISNULL([10], 0) + ISNULL([11], 0) AS [10-11],
ISNULL([12], 0) + ISNULL([13], 0) AS [12-13],
ISNULL([14], 0) + ISNULL([15], 0) AS [14-15],
ISNULL([16], 0) + ISNULL([17], 0) AS [16-17],
ISNULL([18], 0) + ISNULL([19], 0) AS [18-19],
ISNULL([20], 0) + ISNULL([21], 0) AS [20-21],
ISNULL([22], 0) + ISNULL([23], 0) AS [22-23]
FROM (
select *
from
(
select store, CAST(InTime as date) AS ForDate, DATEPART(hour,InTime) AS OnHour, COUNT(*) AS Totals
from Punches
GROUP BY store, CAST(InTime as date),
DATEPART(hour,InTime)
) src
pivot
(
sum(Totals)
for OnHour in ([0],[1], [2], [3],[4], [5], [6],[7],[8], [9], [10],[11], [12], [13],[14], [15], [16],[17],[18], [19],[20],[21], [22], [23])
) piv
) t1
order by store, ForDate
这里是SQL与数据的交互
https://www.db-fiddle.com/f/jo4atDmmj8cshyK1CWWo7x/2
这很疯狂，但值得一试
SELECT storeid, ForDate,
ISNULL([0], 0) + ISNULL([1], 0) AS [0-1],
ISNULL([2], 0) + ISNULL([3], 0) AS [2-3],
ISNULL([4], 0) + ISNULL([5], 0) AS [4-5],
ISNULL([6], 0) + ISNULL([7], 0) AS [6-7],
ISNULL([8], 0) + ISNULL([9], 0) AS [8-9],
ISNULL([10], 0) + ISNULL([11], 0) AS [10-11],
ISNULL([12], 0) + ISNULL([13], 0) AS [12-13],
ISNULL([14], 0) + ISNULL([15], 0) AS [14-15],
ISNULL([16], 0) + ISNULL([17], 0) AS [16-17],
ISNULL([18], 0) + ISNULL([19], 0) AS [18-19],
ISNULL([20], 0) + ISNULL([21], 0) AS [20-21],
ISNULL([22], 0) + ISNULL([23], 0) AS [22-23]
FROM (
select *
from
(
SELECT [Dates].StoreId, [Dates].ForDate, Hours.hour OnHour, COUNT(*) Totals FROM (
SELECT storeId, CAST(InTime as date) AS ForDate FROM Punches
UNION
SELECT storeId, CAST(OutTime AS date) AS ForDate FROM Punches
) [Dates] JOIN (
SELECT * FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23)) hours([hour])
) [Hours] ON 1=1
JOIN
(
SELECT * FROM dbo.Punches
) p
ON p.StoreId = [Dates].StoreId
AND (DATEADD(HOUR, [Hours].[hour], CAST([Dates].ForDate AS DATETIME)) BETWEEN CAST(p.InTime AS DATETIME) AND CAST(p.Outtime AS DATETIME))
GROUP BY [Dates].StoreId, Dates.ForDate, [hour]
) src
pivot
(
sum(Totals)
for OnHour in ([0],[1], [2], [3],[4], [5], [6],[7],[8], [9], [10],[11], [12], [13],[14], [15], [16],[17],[18], [19],[20],[21], [22], [23])
) piv
) t1
order by storeid, ForDate
让我们再深入一点:
我通过这部分生成了所有可能的日期:
SELECT storeId, CAST(InTime as date) AS ForDate FROM Punches
UNION
SELECT storeId, CAST(OutTime AS date) AS ForDate FROM Punches
以及所有可能的时间
SELECT * FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23)) hours([hour])
然后我和他们一起找出所有可能的日期时间。
之后，我只是将它们与冲头连接起来，如果生成的日期小时在inTime和OutTime之间，则通过添加以下条件来计算冲头:
(DATEADD(HOUR, [Hours].[hour], CAST([Dates].ForDate AS DATETIME)) BETWEEN CAST(p.InTime AS DATETIME) AND CAST(p.Outtime AS DATETIME))
其余部分与您的代码完全相同
您可以使用简单的CASE语句来获得您目前为止尝试-
SELECT StoreId, CAST(InTime as Date) as ForDate,
SUM(CASE WHEN DATEPART(hour,InTime) in (0,1) THEN 1 ELSE 0 END) AS [0-1],
SUM(CASE WHEN DATEPART(hour,InTime) in (2,3) THEN 1 ELSE 0 END) AS [2-3],
SUM(CASE WHEN DATEPART(hour,InTime) in (4,5) THEN 1 ELSE 0 END) AS [4-5],
SUM(CASE WHEN DATEPART(hour,InTime) in (6,7) THEN 1 ELSE 0 END) AS [6-7],
SUM(CASE WHEN DATEPART(hour,InTime) in (8,9) THEN 1 ELSE 0 END) AS [8-9],
SUM(CASE WHEN DATEPART(hour,InTime) in (10,11) THEN 1 ELSE 0 END) AS [10-11],
SUM(CASE WHEN DATEPART(hour,InTime) in (12,13) THEN 1 ELSE 0 END) AS [12-13],
SUM(CASE WHEN DATEPART(hour,InTime) in (14,15) THEN 1 ELSE 0 END) AS [14-15],
SUM(CASE WHEN DATEPART(hour,InTime) in (16,17) THEN 1 ELSE 0 END) AS [16-17],
SUM(CASE WHEN DATEPART(hour,InTime) in (18,19) THEN 1 ELSE 0 END) AS [18-19],
SUM(CASE WHEN DATEPART(hour,InTime) in (20,21) THEN 1 ELSE 0 END) AS [20-21],
SUM(CASE WHEN DATEPART(hour,InTime) in (22,23) THEN 1 ELSE 0 END) AS [22-23]
FROM Punches
GROUP BY StoreId, CAST(InTime as Date)
对于您的最终结果使用下面的查询:-
Select StoreId,ForDate,
SUM(CASE WHEN [0-2]>0 THEN 1 ELSE 0 END) AS [0-2],
SUM(CASE WHEN [2-4]>0 THEN 1 ELSE 0 END) AS [2-4],
SUM(CASE WHEN [4-6]>0 THEN 1 ELSE 0 END) AS [4-6],
SUM(CASE WHEN [6-8]>0 THEN 1 ELSE 0 END) AS [6-8],
SUM(CASE WHEN [8-10]>0 THEN 1 ELSE 0 END) AS [8-10],
SUM(CASE WHEN [10-12]>0 THEN 1 ELSE 0 END) AS [10-12],
SUM(CASE WHEN [12-14]>0 THEN 1 ELSE 0 END) AS [12-14],
SUM(CASE WHEN [14-16]>0 THEN 1 ELSE 0 END) AS [14-16],
SUM(CASE WHEN [16-18]>0 THEN 1 ELSE 0 END) AS [16-18],
SUM(CASE WHEN [18-20]>0 THEN 1 ELSE 0 END) AS [18-20],
SUM(CASE WHEN [20-22]>0 THEN 1 ELSE 0 END) AS [20-22],
SUM(CASE WHEN [22-24]>0 THEN 1 ELSE 0 END) AS [22-24]
from
(SELECT StoreId,FirstName+LastName as Name, CAST(InTime as Date) as ForDate,
SUM(CASE WHEN DATEPART(hour,InTime) in (0,1) OR (DATEPART(hour,InTime)<0 AND DATEPART(hour,OutTime)>=1) THEN 1 ELSE 0 END) AS [0-2],
SUM(CASE WHEN DATEPART(hour,InTime) in (2,3) OR (DATEPART(hour,InTime)<2 AND DATEPART(hour,OutTime)>=2) THEN 1 ELSE 0 END) AS [2-4],
SUM(CASE WHEN DATEPART(hour,InTime) in (4,5) OR (DATEPART(hour,InTime)<4 AND DATEPART(hour,OutTime)>=4) THEN 1 ELSE 0 END) AS [4-6],
SUM(CASE WHEN DATEPART(hour,InTime) in (6,7) OR (DATEPART(hour,InTime)<6 AND DATEPART(hour,OutTime)>=6) THEN 1 ELSE 0 END) AS [6-8],
SUM(CASE WHEN DATEPART(hour,InTime) in (8,9) OR (DATEPART(hour,InTime)<8 AND DATEPART(hour,OutTime)>=8) THEN 1 ELSE 0 END) AS [8-10],
SUM(CASE WHEN DATEPART(hour,InTime) in (10,11) OR (DATEPART(hour,InTime)<10 AND DATEPART(hour,OutTime)>=10) THEN 1 ELSE 0 END) AS [10-12],
SUM(CASE WHEN DATEPART(hour,InTime) in (12,13) OR (DATEPART(hour,InTime)<12 AND DATEPART(hour,OutTime)>=12) THEN 1 ELSE 0 END) AS [12-14],
SUM(CASE WHEN DATEPART(hour,InTime) in (14,15) OR (DATEPART(hour,InTime)<14 AND DATEPART(hour,OutTime)>=14) THEN 1 ELSE 0 END) AS [14-16],
SUM(CASE WHEN DATEPART(hour,InTime) in (16,17) OR (DATEPART(hour,InTime)<16 AND DATEPART(hour,OutTime)>=16) THEN 1 ELSE 0 END) AS [16-18],
SUM(CASE WHEN DATEPART(hour,InTime) in (18,19) OR (DATEPART(hour,InTime)<18 AND DATEPART(hour,OutTime)>=18) THEN 1 ELSE 0 END) AS [18-20],
SUM(CASE WHEN DATEPART(hour,InTime) in (20,21) OR (DATEPART(hour,InTime)<20 AND DATEPART(hour,OutTime)>=20) THEN 1 ELSE 0 END) AS [20-22],
SUM(CASE WHEN DATEPART(hour,InTime) in (22,23) OR (DATEPART(hour,InTime)<22 AND DATEPART(hour,OutTime)>=22) THEN 1 ELSE 0 END) AS [22-24]
FROM Punches
GROUP BY StoreId,FirstName+LastName,CAST(InTime as Date)) detailsQuery
GROUP BY StoreId,ForDate