请尝试这个。处理多个休息/员工和个案，当休息仍在进行中或会话未完成时
select [EmployeeId] = [s].[EmployeeId] ,[StartTime] = [s].[ScanDateTime] ,[EndTime] = [et].[ScanDateTime] ,[BreakInMins] = [b].[BreakInMins]from [Scans] as [s] -- here is your tableouter apply ( select top 1 [ScanDateTime], [Id] from [Scans] where [Id] > [s].[Id] and [EmployeeId] = [s].[EmployeeId] and [Status] = 4 order by [ScanDateTime] asc ) as [et]outer apply ( select [BreakInMins] = sum(isnull([r].[mins], datediff(mi, [sp].[ScanDateTime], getdate()))) from [Scans] as [sp] outer apply ( select top 1 [mins] = datediff(mi, [sp].[ScanDateTime], [ScanDateTime]) from [Scans] where [Id] > [sp].[Id] and [EmployeeId] = [sp].[EmployeeId] and [Status] IN (3, 4) order by [ScanDateTime] asc ) as [r] where [sp].[id] > [s].[id] and [sp].[id] < isnull([et].[id], [id] + 1) and [sp].[EmployeeId] = [s].[EmployeeId] and [sp].[Status] = 2 ) as [b] where [Status] = 1;
这是易于测试的脚本：脚本
