CREATE TABLE EmployeeWorkLoad(
EmployeeNr bigint,
Year int,
Month int,
Day int,
Hour int,
Minute int,
CodeNr char(2)
)
Insert into [EmployeeWorkLoad] ( [EmployeeNr],[Year],[Month] ,[Day],[Hour], [Minute] ,[CodeNr]) Values (1,2020,1,4,8,30,'B1'), 
(1,2020,1,4,16,45,'B2'), 
(1,2020,1,6,8,15,'B1'), 
(1,2020,1,6,16,45,'B2'),
(2,2020,3,2,8,10,'B1'), 
(2,2020,3,2,16,5,'B2')
GO

6行受影响

WITH CTE AS (
select EmployeeNr,Year,Month,Day,
MAX(CASE WHEN CodeNr='B1' THEN Hour  END) AS StartHour,
MAX(CASE WHEN CodeNr = 'B1' THEN Minute END) AS StartMinute,
MAX(CASE WHEN CodeNr = 'B2' THEN Hour END) AS EndHour,
MAX(CASE WHEN CodeNr = 'B2' THEN Minute END) AS EndMinute
from EmployeeWorkLoad
group by EmployeeNr,Year,Month,Day )
SELECT * , ABS(EndHour-StartHour) AS DutationHour
,ABS(IIF(EndMinute <StartMinute, EndMinute+60, EndMinute)- StartMinute) AS DurationMinute
FROM
CTE

GO

EmployeeNr | Year | Month | Day | StartHour | StartMinute | EndHour | EndMinute | DutationHour | DurationMinute
---------: | ---: | ----: | --: | --------: | ----------: | ------: | --------: | -----------: | -------------:
         1 | 2020 |     1 |   4 |         8 |          30 |      16 |        45 |            8 |             15
         1 | 2020 |     1 |   6 |         8 |          15 |      16 |        45 |            8 |             30
         2 | 2020 |     3 |   2 |         8 |          10 |      16 |         5 |            8 |             55

db<>在这里摆弄