需要將ClockIn與Clockout匹配，同一天SQL上的多個時鐘-有解無憂

我試圖讓打卡和打卡時間相互對應，以便我可以計算作業時間/休息時間/作業時間內的作業時間等。我有一個看起來像這樣的“時鐘”表：

模擬資料：

時鐘_ID	員工ID	辦公室	時鐘_日期	時鐘時間	活動ID
1	83	比勒陀利亞中央商務區	29/03/2022	06:43:00	1
2	55	比勒陀利亞中央商務區	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	比勒陀利亞中央商務區	2022 年 3 月 30 日	06:46:00	1
20	55	比勒陀利亞中央商務區	2022 年 3 月 30 日	06:51:00	1
21	80	比勒陀利亞中央商務區	2022 年 3 月 30 日	06:54:00	1
22	54	比勒陀利亞中央商務區	2022 年 3 月 30 日	06:54:00	1
23	54	比勒陀利亞中央商務區	2022 年 3 月 30 日	11:24:00	2
24	54	比勒陀利亞中央商務區	2022 年 3 月 30 日	12:11:00	1
25	80	比勒陀利亞中央商務區	2022 年 3 月 30 日	13:03:00	2
26	80	比勒陀利亞中央商務區	2022 年 3 月 30 日	14:10:00	1
27	54	比勒陀利亞中央商務區	2022 年 3 月 30 日	16:01:00	2
28	80	比勒陀利亞中央商務區	2022 年 3 月 30 日	16:01:00	2
29	83	比勒陀利亞中央商務區	2022 年 3 月 30 日	16:01:00	2
30	55	比勒陀利亞中央商務區	2022 年 3 月 30 日	16:05:00	2
31	83	比勒陀利亞中央商務區	2022 年 3 月 31 日	06:48:00	1
32	55	比勒陀利亞中央商務區	2022 年 3 月 31 日	06:53:00	1
33	54	比勒陀利亞中央商務區	2022 年 3 月 31 日	06:55:00	1
34	80	比勒陀利亞中央商務區	2022 年 3 月 31 日	07:09:00	1
35	54	比勒陀利亞中央商務區	2022 年 3 月 31 日	12:02:00	2
36	54	比勒陀利亞中央商務區	2022 年 3 月 31 日	12:09:00	1
37	83	比勒陀利亞中央商務區	2022 年 3 月 31 日	12:34:00	2
38	80	比勒陀利亞中央商務區	2022 年 3 月 31 日	12:34:00	2
39	83	比勒陀利亞中央商務區	2022 年 3 月 31 日	13:09:00	1
40	80	比勒陀利亞中央商務區	2022 年 3 月 31 日	13:09:00	1
41	55	比勒陀利亞中央商務區	2022 年 3 月 31 日	13:32:00	2
42	54	比勒陀利亞中央商務區	2022 年 3 月 31 日	15:56:00	2

ActivityID 確定它是“時鐘輸入”還是“時鐘輸出”

對于任何給定的 Employee_ID，我使用哈希表將資料分成 #clockIN 和 #clockOUT 表，如下所示：

#時鐘輸入

需要將 ClockIn 與 Clock out 匹配，同一天 SQL 上的多個時鐘

#時鐘結束

需要將 ClockIn 與 Clock out 匹配，同一天 SQL 上的多個時鐘

我嘗試在表上的“Clock_Date”上使用內部連接來將時鐘輸入對應到時鐘輸出，但是由于員工可以在一天內多次輸入和輸出時鐘，我得到一個時鐘輸入的多個時鐘輸出：見下文：

需要將 ClockIn 與 Clock out 匹配，同一天 SQL 上的多個時鐘

如您所見，記錄匹配不正確，并且在日期剛剛加入。

我想不出一種正確加入他們的方法，即 7:30 的時鐘輸入必須與同一天最早的時鐘輸出連接，并且當天的下一個時鐘輸入必須是第二個最早的時鐘輸入，它必須與第二個最早的時鐘連接出等

我必須使用游標嗎？如果是這樣，我該如何實施？

我將通過我用來達到這一點的所有 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 只是為了查看該人是否在作業時間內打卡進出。

預先感謝您的任何幫助

uj5u.com熱心網友回復：

也許這可以幫助你

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在這里

結果是這樣

空的	時鐘日期	開始時間	時間結束
55	2022-03-29	06:45:00.0000000	12:18:00.0000000
55	2022-03-29	12:30:00.0000000	空值
83	2022-03-29	06:43:00.0000000	13:03:00.0000000
83	2022-03-29	13:39:00.0000000	16:00:00.0000000

uj5u.com熱心網友回復：

您可以使用視窗函式在基表的單次掃描中執行此操作，無需連接：

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<>小提琴

轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/460378.html

標籤：sql服务器加入内部联接数据库游标

上一篇：總結MySQL中兩個表之間的列

下一篇：使用連接僅對第一個表的值求和一次