Сопряжение даты и времени входа и выхода, а также расчет рабочего времени

Соедините дату и время входа в систему с датой и временем выхода из одной таблицы и рассчитайте рабочее время.

Таблица EmployeeLogInOut:

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

См. образцы данных ниже.

Тип: 1 = вход в систему 2 = выход из системы

Автоматически: 0 = вручную 1 = автоматически

Автоматически происходит, когда сотрудник не выходит из системы в конце дня. Система автоматически отключит сотрудника в конце дня, если он не вышел из системы.

Также возможно, что сотрудник может выйти из системы автоматически, когда происходит обновление программного обеспечения и т. Д.

Для отчета мне нужно рассчитать, сколько минут сотрудник входил в систему каждый день (SalesDate).

Я пытаюсь получить свой результат за столом, например

LogOnTime datetime, 
LogOffTime datetime,
DurationInMinute int,
DepartmentGUID uniqueidentifier

Но поскольку может не существовать соответствующего выхода из системы для каждого входа в систему и наоборот, при таком подходе я получаю сообщение об ошибке.

Мой сценарий:

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')

Благодарим за любую идею :-)

Моя мысль заключалась в том, что если соответствующее время входа в систему не существует для времени выхода из системы, то соответствующее время входа в систему может быть установлено на 06:00 фактической даты продажи.

И если в дате входа в систему отсутствует соответствующая дата и время выхода из системы, она может быть установлена ​​​​на 05:59.

Или просто игнорируйте все записи входа/выхода, которые не соответствуют.

PS: я не могу изменить ни таблицу EmployeeLogInOut, ни то, как в нее заносятся данные.


tsql join row-number datediff
person dlilleaa    schedule 29.11.2011    source источник
comment
Это действительно зависит от требований пользователя. Их волнует, если вы просто игнорируете записи, в которых нет времени входа/выхода? Если нет, то они должны иметь возможность указать, как следует обрабатывать отсутствующие даты и время. Как только вы это узнаете, мы можем создать некоторый SQL, соответствующий требованиям.   -  person Bert    schedule 30.11.2011
comment
Спасибо за комментарий, Бернт. Если возможно, пользователь хочет/должен увидеть, где существует отсутствующий соответствующий вход/выход. Например, LogIn, у которого отсутствует соответствующий LogOut, может просто установить для него значение LogOut, равное NULL, и я мог бы обработать это в файле rdlc. Показывая пропущенное значение или что-то подобное в поле отчета, если LogOut.Value = NULL.   -  person dlilleaa    schedule 30.11.2011

Ответы (1)


arrow_upward
0
arrow_downward

Вот некоторый базовый код, который будет работать с вашими демонстрационными данными, но может потребовать дальнейшей доработки для соответствия вашим бизнес-правилам. Если бы вы могли предоставить образцы данных, которые больше соответствуют вашим бизнес-правилам и ожидаемым результатам в соответствии с этими правилами, я мог бы переработать код для работы с ним.

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
person Carlos Rodriguez    schedule 09.01.2012