需要匹配时钟与时钟，同一天的多个时钟SQL

发布于 2025-01-20 00:00:58 字数 6578 浏览 5 评论 0原文

我试图让上班时间和下班时间相互对应，以便我可以计算工作时间/休息时间/工作时间内的工作时间等。我有一个“时钟”表，如下所示：

MOCK数据：

Clock_ID	员工_ID	办公室	Clock_Date	Clock_Time	ActivityID
1	83	比勒陀利亚 CBD	29/03/2022	06:43:00	1
2	55	比勒陀利亚 CBD	29/03/2022	06:45:00	1
3	54	比勒陀利亚中央商务区	29/03/2022	07:00:00	1
4	80	比勒陀利亚中央商务区	29/03/2022	07:00:00	1
5	75	比勒陀利亚中央商务区	29/03/2022	07:05:00	1
6	54	比勒陀利亚中央商务区	29/03/2022	10:59:00	2
7	54	比勒陀利亚中央商务区	29/03/2022	11:50:00	1
8	55	比勒陀利亚中央商务区	29/03/2022	12:18:00	2
9	55	比勒陀利亚中央商务区	29/ 03/2022	12:30:00	1
10	83	比勒陀利亚中央商务区	29/03/2022	13:03:00	2
11	80	比勒陀利亚中央商务区	29/03/2022	13:04:00	2
12	83	比勒陀利亚中央商务区	29/03/2022	13:39:00	1
13	80	比勒陀利亚中央商务区	29/03/2022	13:39:00	1
14	75	比勒陀利亚中央商务区	29/03/2022	15:59:00	2
15	54	比勒陀利亚中央商务区	29/03/2022	16:00:00	2
16	83	比勒陀利亚中央商务区	29/03/2022	16:00:00	2
17	80	比勒陀利亚中央商务区	29/03/2022	16:00:00	2
18	55	比勒陀利亚中央商务区	29/03/2022	16:00:00	2
19	83	比勒陀利亚中央商务区	30/03/2022	06:46:00	1
20	55	比勒陀利亚中央商务区	30 /03/2022	06:51:00	1
21	80	比勒陀利亚中央商务区	30/03/2022	06:54:00	1
22	54	比勒陀利亚中央商务区	30/03/2022	06:54:00	1
23	54	比勒陀利亚中央商务区	30/03/2022	11:24:00	2
24	54	比勒陀利亚中央商务区	30/03/2022	12:11:00	1
25	80	比勒陀利亚中央商务区	30/03/2022	13:03:00	2
26	80	比勒陀利亚中央商务区	30/03/2022	14:10:00	1
27	54	比勒陀利亚中央商务区	30/03/2022	16:01:00	2
28	80	比勒陀利亚中央商务区	30/03/2022	16:01:00	2
29	83	比勒陀利亚中央商务区	30/03/2022	16:01:00	2
30	55	比勒陀利亚中央商务	区30/03/2022	16:05:00	2
31	83	比勒陀利亚中央商务区	31 /03/2022	06:48:00	1
32	55	比勒陀利亚中央商务区	31/03/2022	06:53:00	1
33	54	比勒陀利亚中央商务区	31/03/2022	06:55:00	1
34	80	比勒陀利亚中央商务区	31/03/2022	07:09:00	1
35	54	比勒陀利亚中央商务区	31/03/2022	12:02:00	2
36	54	比勒陀利亚中央商务区	31/03/2022	12:09:00	1
37	83	比勒陀利亚中央商务区	31/03/2022	12:34:00	2
38	80	比勒陀利亚中央商务区	31/03/2022	12:34:00	2
39	83	比勒陀利亚中央商务区	31/03/2022	13:09:00	1
40	80	比勒陀利亚中央商务区	31/03/2022	13:09:00	1
41	55	比勒陀利亚中央商务区	31/03/2022	13:32:00	2
42	54	比勒陀利亚中央商务区	31 /03/2022	15:56:00	2

ActivityID 确定它是“时钟输入”还是“时钟输出”

我已使用哈希表将任何给定 Employee_ID 的数据分成 #clockIN 和 #clockOUT 表，如下所示：

# ClockIN

#ClockOUT

我尝试使用的内连接表上的“Clock_Date”将时钟输入与时钟输出相对应，但由于员工可以在一天内多次输入和输出时钟，因此我会为一个时钟输入获得多个时钟输出：请参见下文

： sstatic.net/AFib1.png" rel="nofollow noreferrer"> 在此处输入图像描述

作为可以看到，记录匹配不正确，并且是刚刚加入的日期。

我想不出正确加入它们的方法，即 7:30 的 ClockIn 必须与当天最早的时钟加入，并且当天的下一个 ClockIN 必须是第二个最早的 ClockIN，它必须与第二个最早的时钟加入OUT 等。

我必须使用游标吗？如果是这样，我该如何实施？

我将介绍到目前为止我所使用的所有 SQL。

Drop Table #ClockIn
Drop Table #ClockOut
DROP Table #SortedTimes
DROP Table #WorkBoundries

SELECT Clock_ID, Clock_Date AS Date, Clock_Time
    INTO #ClockIn 
    FROM Clocking
    WHERE Employee_ID = 82 AND ActivityID = 1
    SELECT * FROM #ClockIn

SELECT Clock_ID, Clock_Date AS Date, Clock_Time
    INTO #ClockOut 
    FROM Clocking
    WHERE Employee_ID = 82 AND ActivityID = 2
    SELECT * FROM #ClockOut
            
SELECT #ClockIn.Clock_Time As clockIN, #ClockOut.Clock_Time as ClockOUT, #ClockIn.Date INTO #SortedTimes
FROM #ClockIn
INNER JOIN #ClockOut On #ClockOut.Date = #ClockIN.Date
ORDER BY #ClockIn.Date ASC

SELECT * FROM #SortedTimes

SELECT MIN(clockIn)As TimeIn, MAX(ClockOUT) As TimeOut, [Date] As DayWorked INTO #WorkBoundries
FROM #SortedTimes
GROUP BY [Date]
SELECT * FROM #WorkBoundries

#WorkBoundries 只是为了查看该人是否在工作时间内打卡上班/下班。

预先感谢您的任何帮助

原文

I'm trying to get the clock in and clock out times to correspond to each other so that I can calculate hours worked/amount of breaks/time worked within working hours etc. I have a 'Clocking' table that looks like this:

MOCK DATA:

Clock_ID	Employee_ID	Office	Clock_Date	Clock_Time	ActivityID
1	83	Pretoria CBD	29/03/2022	06:43:00	1
2	55	Pretoria CBD	29/03/2022	06:45:00	1
3	54	Pretoria CBD	29/03/2022	07:00:00	1
4	80	Pretoria CBD	29/03/2022	07:00:00	1
5	75	Pretoria CBD	29/03/2022	07:05:00	1
6	54	Pretoria CBD	29/03/2022	10:59:00	2
7	54	Pretoria CBD	29/03/2022	11:50:00	1
8	55	Pretoria CBD	29/03/2022	12:18:00	2
9	55	Pretoria CBD	29/03/2022	12:30:00	1
10	83	Pretoria CBD	29/03/2022	13:03:00	2
11	80	Pretoria CBD	29/03/2022	13:04:00	2
12	83	Pretoria CBD	29/03/2022	13:39:00	1
13	80	Pretoria CBD	29/03/2022	13:39:00	1
14	75	Pretoria CBD	29/03/2022	15:59:00	2
15	54	Pretoria CBD	29/03/2022	16:00:00	2
16	83	Pretoria CBD	29/03/2022	16:00:00	2
17	80	Pretoria CBD	29/03/2022	16:00:00	2
18	55	Pretoria CBD	29/03/2022	16:00:00	2
19	83	Pretoria CBD	30/03/2022	06:46:00	1
20	55	Pretoria CBD	30/03/2022	06:51:00	1
21	80	Pretoria CBD	30/03/2022	06:54:00	1
22	54	Pretoria CBD	30/03/2022	06:54:00	1
23	54	Pretoria CBD	30/03/2022	11:24:00	2
24	54	Pretoria CBD	30/03/2022	12:11:00	1
25	80	Pretoria CBD	30/03/2022	13:03:00	2
26	80	Pretoria CBD	30/03/2022	14:10:00	1
27	54	Pretoria CBD	30/03/2022	16:01:00	2
28	80	Pretoria CBD	30/03/2022	16:01:00	2
29	83	Pretoria CBD	30/03/2022	16:01:00	2
30	55	Pretoria CBD	30/03/2022	16:05:00	2
31	83	Pretoria CBD	31/03/2022	06:48:00	1
32	55	Pretoria CBD	31/03/2022	06:53:00	1
33	54	Pretoria CBD	31/03/2022	06:55:00	1
34	80	Pretoria CBD	31/03/2022	07:09:00	1
35	54	Pretoria CBD	31/03/2022	12:02:00	2
36	54	Pretoria CBD	31/03/2022	12:09:00	1
37	83	Pretoria CBD	31/03/2022	12:34:00	2
38	80	Pretoria CBD	31/03/2022	12:34:00	2
39	83	Pretoria CBD	31/03/2022	13:09:00	1
40	80	Pretoria CBD	31/03/2022	13:09:00	1
41	55	Pretoria CBD	31/03/2022	13:32:00	2
42	54	Pretoria CBD	31/03/2022	15:56:00	2

The ActivityID determines whether it is a 'Clock IN' or 'Clock OUT'

I have used hash tables to separate the data into #clockIN and #clockOUT tables for any given Employee_ID as seen below:

#ClockIN

#ClockOUT

I tried using an inner join on the 'Clock_Date' on the tables to correspond clockINs to clockOUTs, but I get multiple clockOUTS for one clockIN due to the fact that employees can clockIN and OUT multiple times in a day: See below:

As you can see, the records are matched incorrectly, and a just joined on the date.

I cannot think of a way to correctly join them I.e ClockIn at 7:30 must be joined with the EARLIEST clock out on the same day and the next clockIN for that day must be the second earliest ClockIN which must be joined with the second earliest Clock OUT etc.

Would I have to use a cursor? if so, how could I implement that?

I will past all the SQL I used to get to this point.

Drop Table #ClockIn
Drop Table #ClockOut
DROP Table #SortedTimes
DROP Table #WorkBoundries

SELECT Clock_ID, Clock_Date AS Date, Clock_Time
    INTO #ClockIn 
    FROM Clocking
    WHERE Employee_ID = 82 AND ActivityID = 1
    SELECT * FROM #ClockIn

SELECT Clock_ID, Clock_Date AS Date, Clock_Time
    INTO #ClockOut 
    FROM Clocking
    WHERE Employee_ID = 82 AND ActivityID = 2
    SELECT * FROM #ClockOut
            
SELECT #ClockIn.Clock_Time As clockIN, #ClockOut.Clock_Time as ClockOUT, #ClockIn.Date INTO #SortedTimes
FROM #ClockIn
INNER JOIN #ClockOut On #ClockOut.Date = #ClockIN.Date
ORDER BY #ClockIn.Date ASC

SELECT * FROM #SortedTimes

SELECT MIN(clockIn)As TimeIn, MAX(ClockOUT) As TimeOut, [Date] As DayWorked INTO #WorkBoundries
FROM #SortedTimes
GROUP BY [Date]
SELECT * FROM #WorkBoundries

The #WorkBoundries is just to see if the person is clocking IN/OUT within their work hours or not.

Thank you in advance for any assistance

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

城歌 2025-01-27 00:00:58

也许这可以帮助您

select e.empid,
       e.clockdate,
       e.clocktime as starttime,
       o.clocktime as endtime
from   emp e
  outer apply (select top 1 e2.clocktime 
               from   emp e2 
               where  e2.empid = e.empid
               and    e2.activity = 2
               and    e2.clockdate = e.clockdate
               and    e2.clocktime > e.clocktime
               order by e2.clocktime
              ) o
where  e.activity = 1
order by e.empid, e.clocktime

；

a5c3a84446f929dd2c50d115115ca0303030ce57	fiddle	=	”
amp	& -03-29	06：45：00.0000000	12：18：00.0000000
55	2022-03-29	12：30：00.0000000	null
83	2022-03-29	06：43：00.0000000	13：03：03：00.00.00.0000 000
83	2022-03-29	13： 00.0000 000 39：00.0000000	16：00：00.0000000

Maybe this can help you

select e.empid,
       e.clockdate,
       e.clocktime as starttime,
       o.clocktime as endtime
from   emp e
  outer apply (select top 1 e2.clocktime 
               from   emp e2 
               where  e2.empid = e.empid
               and    e2.activity = 2
               and    e2.clockdate = e.clockdate
               and    e2.clocktime > e.clocktime
               order by e2.clocktime
              ) o
where  e.activity = 1
order by e.empid, e.clocktime

DBFiddle here

it results in this

empid	clockdate	starttime	endtime
55	2022-03-29	06:45:00.0000000	12:18:00.0000000
55	2022-03-29	12:30:00.0000000	null
83	2022-03-29	06:43:00.0000000	13:03:00.0000000
83	2022-03-29	13:39:00.0000000	16:00:00.0000000

回复收藏 0 原文

-黛色若梦 2025-01-27 00:00:58

您可以使用窗口函数在基表的单次扫描中执行此操作，并且无需连接：

WITH NextValues AS (
    SELECT *,
      NextClockOut = LEAD(CASE WHEN c.ActivityID = 2 THEN c.Clock_Time END)
        OVER (PARTITION BY c.Office, c.Employee_ID, c.Clock_Date
             ORDER BY c.Clock_Time)
    FROM Clocking c
)
SELECT
  nv.Employee_ID,
  nv.Office,
  nv.Clock_Date,
  ClockIn = nv.Clock_Time,
  ClockOut = nv.NextClockOut
FROM NextValues nv
WHERE nv.ActivityID = 1;

db<>fiddle

You can do this in a single scan of the base table, and no joins, by using window functions:

WITH NextValues AS (
    SELECT *,
      NextClockOut = LEAD(CASE WHEN c.ActivityID = 2 THEN c.Clock_Time END)
        OVER (PARTITION BY c.Office, c.Employee_ID, c.Clock_Date
             ORDER BY c.Clock_Time)
    FROM Clocking c
)
SELECT
  nv.Employee_ID,
  nv.Office,
  nv.Clock_Date,
  ClockIn = nv.Clock_Time,
  ClockOut = nv.NextClockOut
FROM NextValues nv
WHERE nv.ActivityID = 1;

db<>fiddle

回复收藏 0 原文

~没有更多了~