配对登录和注销datetime，计算工作时间

Question

将单个表中的登录日期时间与注销日期时间配对，计算工作时间

EmployeeLogInOut表:

TransactionID bigint,
TransactionDate datetime,
Type smallint,
Automatic bit,
SalesDate datetime,
EmployeeGUID uniqueidentifier,
DepartmentGUID uniqueidentifier

见下面的示例数据。

类型:1 = LogIn 2 = LogOut

自动:0 =手动1 =自动

当员工在一天结束时没有注销时自动发生。如果员工在一天结束时没有注销，系统将自动注销该员工。

当软件更新发生时，雇员也可能自动注销…

对于报告，我需要计算Employee每天(SalesDate)登录到系统的分钟数。

我试图得到我的结果到表，如

LogOnTime datetime, 
LogOffTime datetime,
DurationInMinute int,
DepartmentGUID uniqueidentifier

但是因为每个logIn可能不存在相应的logOut，反之亦然，所以我在这种方法上得到了一个错误。

我的脚本:

declare @EmployeeGUID uniqueidentifier
declare @StartDate datetime
declare @EndDate datetime
set @EmployeeGUID = 'C335F76A-E757-48D9-8DFE-01096EEA6A71'
set @StartDate = '09-01-2011'
set @EndDate = '09-30-2011'

create table #result
(
    LogOnTime datetime, 
    LogOffTime datetime,
    DurationInMinute int,
    DepartmentGUID uniqueidentifier
)

    Insert #result(LogOnTime,LogOffTime,DurationInMinute,DepartmentGUID  ) 
    Select A.TransactionDate, B.TransactionDate,datediff(minute,A.TransactionDate, isnull(B.TransactionDate,GetDate())),A.DepartmentGUID
       from 
        (Select Row_number() over (order by TransactionDate) as Num ,* from EmployeeLogInOut 
         where [Type]=1 and  EmployeeGUID = @EmployeeGUID and SalesDate between @StartDate and @EndDate ) as A
        LEFT JOIN  
        (Select Row_number() over (order by TransactionDate) as Num,* from EmployeeLogInOut 
         where [Type]=2   and EmployeeGUID = @EmployeeGUID and SalesDate between @StartDate and @EndDate ) as B
        ON A.Num = B.Num 
    select * from #result
    drop table #result

样本数据:

CREATE TABLE EmployeeLogInOut(
    [TransactionID] [bigint] NOT NULL,
    [TransactionDate] [datetime] NOT NULL,
    [Type] [smallint] NOT NULL,
    [Automatic] [bit] NOT NULL,
    [SalesDate] [datetime] NOT NULL,
    [EmployeeGUID] [uniqueidentifier] NOT NULL,
    [DepartmentGUID] [uniqueidentifier] NOT NULL
)
INSERT INTO EmployeeLogInOut VALUES 
(2006,'2011-09-05 16:59:39.000',1,0,'2011-09-05 00:00:00.000','C335F76A-E757-48D9-8DFE-01096EEA6A71','520EEFD4-DC30-4390-BB7F-FEFD83D9576A'),
(2008,'2011-09-05 21:57:22.000',2,0,'2011-09-05 00:00:00.000','C335F76A-E757-48D9-8DFE-01096EEA6A71','520EEFD4-DC30-4390-BB7F-FEFD83D9576A'),
(2019,'2011-09-06 16:59:37.000',1,0,'2011-09-06 00:00:00.000','C335F76A-E757-48D9-8DFE-01096EEA6A71','520EEFD4-DC30-4390-BB7F-FEFD83D9576A'),
(2022,'2011-09-06 17:35:41.430',2,0,'2011-09-06 00:00:00.000','C335F76A-E757-48D9-8DFE-01096EEA6A71','520EEFD4-DC30-4390-BB7F-FEFD83D9576A'),
(2039,'2011-09-06 17:36:41.000',2,1,'2011-09-06 00:00:00.000','C335F76A-E757-48D9-8DFE-01096EEA6A71','520EEFD4-DC30-4390-BB7F-FEFD83D9576A'),
(2023,'2011-09-06 17:37:41.000',1,0,'2011-09-06 00:00:00.000','C335F76A-E757-48D9-8DFE-01096EEA6A71','520EEFD4-DC30-4390-BB7F-FEFD83D9576A'),
(2037,'2011-09-07 00:45:32.000',2,0,'2011-09-06 00:00:00.000','C335F76A-E757-48D9-8DFE-01096EEA6A71','520EEFD4-DC30-4390-BB7F-FEFD83D9576A'),
(2054,'2011-09-08 17:12:19.000',1,0,'2011-09-08 00:00:00.000','C335F76A-E757-48D9-8DFE-01096EEA6A71','520EEFD4-DC30-4390-BB7F-FEFD83D9576A'),
(2059,'2011-09-08 20:58:17.000',2,0,'2011-09-08 00:00:00.000','C335F76A-E757-48D9-8DFE-01096EEA6A71','520EEFD4-DC30-4390-BB7F-FEFD83D9576A'),
(2262,'2011-09-20 20:09:10.000',1,0,'2011-09-20 00:00:00.000','C335F76A-E757-48D9-8DFE-01096EEA6A71','520EEFD4-DC30-4390-BB7F-FEFD83D9576A'),
(2269,'2011-09-21 06:59:00.000',2,1,'2011-09-20 00:00:00.000','C335F76A-E757-48D9-8DFE-01096EEA6A71','520EEFD4-DC30-4390-BB7F-FEFD83D9576A'),
(2278,'2011-09-21 17:06:49.000',1,0,'2011-09-21 00:00:00.000','C335F76A-E757-48D9-8DFE-01096EEA6A71','520EEFD4-DC30-4390-BB7F-FEFD83D9576A'),
(2282,'2011-09-21 22:05:29.000',2,0,'2011-09-21 00:00:00.000','C335F76A-E757-48D9-8DFE-01096EEA6A71','520EEFD4-DC30-4390-BB7F-FEFD83D9576A'),
(2283,'2011-09-21 22:06:55.000',1,0,'2011-09-21 00:00:00.000','C335F76A-E757-48D9-8DFE-01096EEA6A71','520EEFD4-DC30-4390-BB7F-FEFD83D9576A'),
(2284,'2011-09-21 22:09:04.000',2,0,'2011-09-21 00:00:00.000','C335F76A-E757-48D9-8DFE-01096EEA6A71','520EEFD4-DC30-4390-BB7F-FEFD83D9576A')

如有任何意见，不胜感激:-)

我的想法是，如果对应的LogIn Datetime不存在于LogOut时间，那么对应的LogIn Datetime可以设置为实际salesDate的06:00。

如果一个LogIn Datetime缺少相应的LogOut Datetime，它可以被设置为05:59

或者忽略所有不对应的LogIn/LogOut条目。

PS:我不能改变EmployeeLogInOut表，也不能改变数据输入的方式。

Answer 1

下面是一些基本代码，这些代码将对您的示例数据起作用，但可能需要进一步细化以处理您的业务规则。如果您能提供更接近您的业务规则和根据这些规则的预期结果的示例数据，我可以重新编写代码以使用它。

WITH Ranked AS (
--Ranking the rows to clean-up the data.
SELECT  TransactionDate,
        [Type],
        SalesDate,
        --EmployeeGUID and DepartmentGUID should be added to the PARTITION BY section in a real scenario. For the sample data they are irrelevant.
        Sequence = ROW_NUMBER() OVER(PARTITION BY SalesDate ORDER BY TransactionDate)
FROM    #EmployeeLogInOut
), Clean AS (
--A more complex clean-up process can be in place here. Right now it just eliminates transactions where the type doesn't alternate from the previous one.
SELECT  TransactionDate,
        [Type],
        SalesDate
FROM    Ranked AS R
WHERE   NOT EXISTS (SELECT 1 FROM Ranked WHERE SalesDate = R.SalesDate AND Type = R.Type AND Sequence = R.Sequence - 1)
--Previous CTEs can be bypassed and we can reference #EmployeeLogInOut instead of Clean, but we would get some "wrong" output for SalesDate 2011-09-06
), LI AS (
SELECT  SalesDate,
        LogInTime = TransactionDate,
        --EmployeeGUID and DepartmentGUID should be added to the PARTITION BY section in a real scenario. For the sample data they are irrelevant.
        Sequence = ROW_NUMBER() OVER (PARTITION BY SalesDate ORDER BY TransactionDate)
FROM    Clean
WHERE   [Type] = 1
), LO AS (
SELECT  SalesDate,
        LogOutTime = TransactionDate,
        --EmployeeGUID and DepartmentGUID should be added to the PARTITION BY section in a real scenario. For the sample data they are irrelevant.
        Sequence = ROW_NUMBER() OVER (PARTITION BY SalesDate ORDER BY TransactionDate)
FROM    Clean
WHERE   [Type] = 2
)
SELECT  LI.SalesDate,
    LI.LogInTime,
    LO.LogOutTime,
    WorkTime = DATEDIFF(MINUTE, LI.LoginTime, LO.LogoutTime)
FROM    LI 
    LEFT JOIN LO ON LO.SalesDate = LI.SalesDate
        AND LI.Sequence = LO.Sequence;

这会产生以下结果:

SalesDate   LogInTime   LogOutTime  WorkTime
2011-09-05 00:00:00.000 2011-09-05 16:59:39.000 2011-09-05 21:57:22.000 298
2011-09-06 00:00:00.000 2011-09-06 16:59:37.000 2011-09-06 17:35:41.430 36
2011-09-06 00:00:00.000 2011-09-06 17:37:41.000 2011-09-07 00:45:32.000 428
2011-09-08 00:00:00.000 2011-09-08 17:12:19.000 2011-09-08 20:58:17.000 226
2011-09-20 00:00:00.000 2011-09-20 20:09:10.000 2011-09-21 06:59:00.000 650
2011-09-21 00:00:00.000 2011-09-21 17:06:49.000 2011-09-21 22:05:29.000 299
2011-09-21 00:00:00.000 2011-09-21 22:06:55.000 2011-09-21 22:09:04.000 3