SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Create View View_X AS (
    SELECT '000130' as empno, '1' as attendanttype , '2023-07-12 03:55:04.000' as recorddatetime
		UNION all 
	SELECT '000130' as empno, '1' as attendanttype , '2023-07-12 03:55:06.000' as recorddatetime
		UNION all 	
	SELECT '000130' as empno, '2' as attendanttype , '2023-07-12 05:01:04.000' as recorddatetime
		UNION all 
	SELECT '000130' as empno, '2' as attendanttype , '2023-07-12 05:05:04.000' as recorddatetime
		UNION all 
	SELECT '000130' as empno, '1' as attendanttype , '2023-07-12 06:55:04.000' as recorddatetime
		UNION all 
	SELECT '000131' as empno, '2' as attendanttype , '2023-07-12 03:55:07.000' as recorddatetime
		UNION all 
	SELECT '000131' as empno, '2' as attendanttype , '2023-07-12 03:55:05.000' as recorddatetime
    -- 額外加入
        UNION ALL
    SELECT '000132' as empno, '1' as attendanttype , '2023-07-12 04:55:04.000' as recorddatetime  
        UNION ALL
    SELECT '000132' as empno, '1' as attendanttype , '2023-07-12 04:55:08.000' as recorddatetime  
    -- 額外加入
        UNION ALL
    SELECT '000133' as empno, '2' as attendanttype , '2023-07-12 04:55:04.000' as recorddatetime  
        UNION ALL
    SELECT '000133' as empno, '2' as attendanttype , '2023-07-12 07:55:08.000' as recorddatetime  
  )
Select *
From View_X


empno
attendanttype
recorddatetime


000130
1
2023-07-12 03:55:04.000

000130
1
2023-07-12 03:55:06.000

000130
2
2023-07-12 05:01:04.000

000130
2
2023-07-12 05:05:04.000

000130
1
2023-07-12 06:55:04.000

000131
2
2023-07-12 03:55:07.000

000131
2
2023-07-12 03:55:05.000

000132
1
2023-07-12 04:55:04.000

000132
1
2023-07-12 04:55:08.000

000133
2
2023-07-12 04:55:04.000

000133
2
2023-07-12 07:55:08.000


-- 定義上班時段
Create View View_Z AS (
  Select 1 AS No_P,'00:00:00' AS Start_P,'04:00:00' AS End_P
  Union All
  -- 第一段上班時段 : 04 - 05
  Select 2 AS No_P,'04:00:00' AS Start_P,'05:00:00' AS TEnd_P  
  Union All
  Select 3 AS No_P,'05:00:00' AS Start_P,'07:00:00' AS TEnd_P
  Union All
  -- 第二段上班時段 : 07 - 08
  Select 4 AS No_P,'07:00:00' AS Start_P,'08:00:00' AS TEnd_P  
  Union All
  Select 5 AS No_P,'08:00:00' AS Start_P,'12:00:00' AS TEnd_P    
) 
Select *
From View_Z


No_P
Start_P
End_P


1
00:00:00
04:00:00

2
04:00:00
05:00:00

3
05:00:00
07:00:00

4
07:00:00
08:00:00

5
08:00:00
12:00:00


-- 上班
Select *,Case When No_P = 1 Or No_P = 3 Then N'準時上班' Else N'遲到' End As Status
From (
Select View_X.*,No_P,
row_number() over (partition by empno,attendanttype,No_P order by recorddatetime) AS No,  
CONVERT(varchar(10),cast(recorddatetime AS DateTime),111) AS Date_X,
CONVERT(varchar(12),cast(recorddatetime AS DateTime), 108) AS Time_X
From View_X
Left Join View_Z On CONVERT(varchar(12),cast(View_X.recorddatetime AS DateTime), 108) > View_Z.Start_P
And CONVERT(varchar(12),cast(View_X.recorddatetime AS DateTime), 108) < View_Z.End_P
Where attendanttype = '1'
) AS QQ
Where No = 1
Order By empno,attendanttype,recorddatetime


empno
attendanttype
recorddatetime
No_P
No
Date_X
Time_X
Status


000130
1
2023-07-12 03:55:04.000
1
1
2023/07/12
03:55:04
準時上班

000130
1
2023-07-12 06:55:04.000
3
1
2023/07/12
06:55:04
準時上班

000132
1
2023-07-12 04:55:04.000
2
1
2023/07/12
04:55:04
遲到


-- 下班
Select *,Case When No_P = 1 Or No_P = 3 Then N'準時下班' Else N'早退' End As Status
From (
Select View_X.*,No_P,
row_number() over (partition by empno,attendanttype,No_P order by recorddatetime) AS No,  
CONVERT(varchar(10),cast(recorddatetime AS DateTime),111) AS Date_X,
CONVERT(varchar(12),cast(recorddatetime AS DateTime), 108) AS Time_X
From View_X
Left Join View_Z On CONVERT(varchar(12),cast(View_X.recorddatetime AS DateTime), 108) > View_Z.Start_P
And CONVERT(varchar(12),cast(View_X.recorddatetime AS DateTime), 108) < View_Z.End_P
Where attendanttype = '2'
) AS QQ
Where No = 1
Order By empno,attendanttype,recorddatetime


empno
attendanttype
recorddatetime
No_P
No
Date_X
Time_X
Status


000130
2
2023-07-12 05:01:04.000
3
1
2023/07/12
05:01:04
準時下班

000131
2
2023-07-12 03:55:05.000
1
1
2023/07/12
03:55:05
準時下班

000133
2
2023-07-12 04:55:04.000
2
1
2023/07/12
04:55:04
早退

000133
2
2023-07-12 07:55:08.000
4
1
2023/07/12
07:55:08
早退

empno	attendanttype	recorddatetime
000130	1	2023-07-12 03:55:04.000
000130	1	2023-07-12 03:55:06.000
000130	2	2023-07-12 05:01:04.000
000130	2	2023-07-12 05:05:04.000
000130	1	2023-07-12 06:55:04.000
000131	2	2023-07-12 03:55:07.000
000131	2	2023-07-12 03:55:05.000
000132	1	2023-07-12 04:55:04.000
000132	1	2023-07-12 04:55:08.000
000133	2	2023-07-12 04:55:04.000
000133	2	2023-07-12 07:55:08.000

No_P	Start_P	End_P
1	00:00:00	04:00:00
2	04:00:00	05:00:00
3	05:00:00	07:00:00
4	07:00:00	08:00:00
5	08:00:00	12:00:00

empno	attendanttype	recorddatetime	No_P	No	Date_X	Time_X	Status
000130	1	2023-07-12 03:55:04.000	1	1	2023/07/12	03:55:04	準時上班
000130	1	2023-07-12 06:55:04.000	3	1	2023/07/12	06:55:04	準時上班
000132	1	2023-07-12 04:55:04.000	2	1	2023/07/12	04:55:04	遲到