Блог

Главная - Вопросы по программированию - SQL-запрос для сопоставления строк для образца отчета об управлении посещаемостью

SQL-запрос для сопоставления строк для образца отчета об управлении посещаемостью

#sql #sql-server #sql-server-2005

#sql #sql-сервер #sql-server-2005

Вопрос:

У меня есть таблица журнала доступа для системы управления посещаемостью, подобная этой:

     ID      LogDate              EmployeeId Direction
    242     2014-05-02 07:59:20.000 2565    in
    1907    2014-05-02 19:00:01.000 2565    out
    3648    2014-05-03 08:08:03.000 2565    in
    5611    2014-05-03 19:32:37.000 2565    out
    5612    2014-05-03 19:33:44.000 2565    out
    7315    2014-05-05 08:01:01.000 2565    in
    9301    2014-05-05 19:37:48.000 2565    out
    10857   2014-05-06 08:29:44.000 2565    in
    12327   2014-05-06 18:51:56.000 2565    out
    14331   2014-05-07 08:44:03.000 2565    in
    16084   2014-05-07 19:09:07.000 2565    out
    17608   2014-05-08 07:58:13.000 2565    in
    21155   2014-05-09 08:12:26.000 2565    in
    22673   2014-05-09 18:42:55.000 2565    out
    24550   2014-05-10 08:13:09.000 2565    in
    26455   2014-05-10 19:02:28.000 2565    out
    30013   2014-05-12 19:07:53.000 2565    out
    31083   2014-05-13 08:00:27.000 2565    in
    31087   2014-05-13 08:02:39.000 2565    in
    33084   2014-05-13 18:58:09.000 2565    out
    35201   2014-05-14 08:31:13.000 2565    in
    37238   2014-05-14 19:10:03.000 2565    out
    39225   2014-05-15 08:47:44.000 2565    in
    40636   2014-05-15 18:59:47.000 2565    out
    42969   2014-05-16 09:58:17.000 2565    in
    42970   2014-05-16 09:58:24.000 2565    in
    42974   2014-05-16 09:58:51.000 2565    in
    44114   2014-05-16 18:49:39.000 2565    out
    44116   2014-05-16 18:50:43.000 2565    out
    46284   2014-05-17 09:07:03.000 2565    in
    47323   2014-05-17 17:56:57.000 2565    out
    48719   2014-05-19 08:05:28.000 2565    in
    49919   2014-05-19 18:52:35.000 2565    out
    51188   2014-05-20 09:02:07.000 2565    in
    52348   2014-05-20 19:01:05.000 2565    out
    54231   2014-05-21 09:35:49.000 2565    in
    55896   2014-05-21 19:57:48.000 2565    out

У меня есть следующие условия для получения продолжительности для образца 12 часов сведения о журнале смены:

  1. Идеальный случай: где каждый вход и соответствующий выход (направления) должны быть сопоставлены, а продолжительность должна быть рассчитана путем вычета времени выхода из времени поступления
  2. Вход, нет выхода, вход: при этом для входа нет записи о выходе, и за ней следует другой вход. Отображать значение NULL и, следовательно, длительность не может быть вычислена, поскольку отсутствует выход, давайте поставим 12:00:00 (продолжительность смены).
  3. Вход, выход, выход: длительность должна быть равна самому раннему выходу минус последнему входу.
  4. Вход, выход, вход, выход: продолжительность = первый выход — первый вход второй выход — второй вход.

Примечание: это для 12-часовой смены, и обратите внимание, что при входе 20 июля 2014 года в 19:00:00 часов выход может произойти 21 июля 2014 года в 07:05:00, а продолжительность составляет 12:05:00 часов.

Я написал Sqll для получения результата, но не могу выполнить определенные условия: вот тестовые данные:

 IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tmpAccessLogTbl]') AND type in (N'U'))
DROP TABLE [dbo].[tmpAccessLogTbl]
GO

CREATE TABLE  tmpAccessLogTbl   (
    [ID] [int] NOT NULL PRIMARY KEY,
    [LogDate] [datetime] NOT NULL,
    [EmployeeId] [int] NOT NULL,
    [Direction] [nvarchar](3) NULL
) 
;



INSERT INTO tmpAccessLogTbl ([ID], [LogDate], [EmployeeId], [Direction])
SELECT 242, '20140502 07:59:20.000', 2565, N'in' UNION ALL
SELECT 1907, '20140502 19:00:01.000', 2565, N'out' UNION ALL
SELECT 3648, '20140503 08:08:03.000', 2565, N'in' UNION ALL
SELECT 5611, '20140503 19:32:37.000', 2565, N'out' UNION ALL
SELECT 5612, '20140503 19:33:44.000', 2565, N'out' UNION ALL
SELECT 7315, '20140505 08:01:01.000', 2565, N'in' UNION ALL
SELECT 9301, '20140505 19:37:48.000', 2565, N'out' UNION ALL
SELECT 10857, '20140506 08:29:44.000', 2565, N'in' UNION ALL
SELECT 12327, '20140506 18:51:56.000', 2565, N'out' UNION ALL
SELECT 14331, '20140507 08:44:03.000', 2565, N'in' UNION ALL
SELECT 16084, '20140507 19:09:07.000', 2565, N'out' UNION ALL
SELECT 17608, '20140508 07:58:13.000', 2565, N'in' UNION ALL
SELECT 21155, '20140509 08:12:26.000', 2565, N'in' UNION ALL
SELECT 22673, '20140509 18:42:55.000', 2565, N'out' UNION ALL
SELECT 24550, '20140510 08:13:09.000', 2565, N'in' UNION ALL
SELECT 26455, '20140510 19:02:28.000', 2565, N'out' UNION ALL
SELECT 30013, '20140512 19:07:53.000', 2565, N'out' UNION ALL
SELECT 31083, '20140513 08:00:27.000', 2565, N'in' UNION ALL
SELECT 31087, '20140513 08:02:39.000', 2565, N'in' UNION ALL
SELECT 33084, '20140513 18:58:09.000', 2565, N'out' UNION ALL
SELECT 35201, '20140514 08:31:13.000', 2565, N'in' UNION ALL
SELECT 37238, '20140514 19:10:03.000', 2565, N'out' UNION ALL
SELECT 39225, '20140515 08:47:44.000', 2565, N'in' UNION ALL
SELECT 40636, '20140515 18:59:47.000', 2565, N'out' UNION ALL
SELECT 42969, '20140516 09:58:17.000', 2565, N'in' UNION ALL
SELECT 42970, '20140516 09:58:24.000', 2565, N'in' UNION ALL
SELECT 42974, '20140516 09:58:51.000', 2565, N'in' UNION ALL
SELECT 44114, '20140516 18:49:39.000', 2565, N'out' UNION ALL
SELECT 44116, '20140516 18:50:43.000', 2565, N'out' UNION ALL
SELECT 46284, '20140517 09:07:03.000', 2565, N'in' UNION ALL
SELECT 47323, '20140517 17:56:57.000', 2565, N'out'
;


SELECT t.EmployeeId,
       t.AttendanceDate,
       t.InTime,
       CASE 
            WHEN DATEDIFF(HOUR, t.InTime, t.OutTime) > 23 THEN NULL
            ELSE t.OutTime
       END  AS OutTime,
       CAST (DATEDIFF(HOUR, t.InTime, t.OutTime) AS VARCHAR)   ':'    CAST(DATEDIFF(minute, t.InTime, t.OutTime) %60  AS VARCHAR) AS Duration
FROM   (
           SELECT i.EmployeeId,
                  CONVERT(VARCHAR, i.LogDate, 106) AS AttendanceDate,
                  i.LogDate  AS InTime,
                  (
                      SELECT MIN(o.LogDate) AS OutTime
                      FROM   tmpAccessLogTbl o
                      WHERE  o.Direction = 'Out'
                             AND o.EmployeeId = i.EmployeeId
                             AND o.LogDate > i.LogDate
                  )          AS OutTime
           FROM   tmpAccessLogTbl       i
           WHERE  i.Direction = 'In'
       )    AS t
ORDER BY
       t.EmployeeId,
       t.AttendanceDate

Мои результаты следующие из этого Sql:

 EmployeeId  AttendanceDate     InTime       OutTime                 Duration
2565    02 May 2014 2014-05-02 07:59:20.000 2014-05-02 19:00:01.000 12:1
2565    03 May 2014 2014-05-03 08:08:03.000 2014-05-03 19:32:37.000 11:24
2565    05 May 2014 2014-05-05 08:01:01.000 2014-05-05 19:37:48.000 11:36
2565    06 May 2014 2014-05-06 08:29:44.000 2014-05-06 18:51:56.000 10:22
2565    07 May 2014 2014-05-07 08:44:03.000 2014-05-07 19:09:07.000 11:25
2565    08 May 2014 2014-05-08 07:58:13.000 NULL                    35:44
2565    09 May 2014 2014-05-09 08:12:26.000 2014-05-09 18:42:55.000 10:30
2565    10 May 2014 2014-05-10 08:13:09.000 2014-05-10 19:02:28.000 11:49
2565    13 May 2014 2014-05-13 08:00:27.000 2014-05-13 18:58:09.000 10:58
2565    13 May 2014 2014-05-13 08:02:39.000 2014-05-13 18:58:09.000 10:56
2565    14 May 2014 2014-05-14 08:31:13.000 2014-05-14 19:10:03.000 11:39
2565    15 May 2014 2014-05-15 08:47:44.000 2014-05-15 18:59:47.000 10:12
2565    16 May 2014 2014-05-16 09:58:17.000 2014-05-16 18:49:39.000 9:51
2565    16 May 2014 2014-05-16 09:58:24.000 2014-05-16 18:49:39.000 9:51
2565    16 May 2014 2014-05-16 09:58:51.000 2014-05-16 18:49:39.000 9:51
2565    17 May 2014 2014-05-17 09:07:03.000 2014-05-17 17:56:57.000 8:49

Подводя итог, я приведу результаты моего приведенного выше Sql и ожидаемые результаты здесь:

                          -- My Current Query  --     --  Expected Results --
Sl  EmpID     Day       InTime  OutTime Duration    InTime  OutTime Duration
1   2565    02-May-2014 7:59    19:00   11:00:41    7:59    19:00   11:00:41
2   2565    03-May-2014 8:08    19:32   11:24:34    8:08    19:32   11:24:34
3   2565    05 May 2014 8:01    19:37   11:36:47    8:01    19:37   11:36:47
4   2565    06 May 2014 8:29    18:51   10:22:12    8:29    18:51   10:22:12
5   2565    07 May 2014 8:44    19:09   10:25:04    8:44    19:09   10:25:04
6   2565    08 May 2014 7:58    NULL    NULL        7:58            12:00:00
7   2565    09 May 2014 8:12    18:42   10:30:29    8:12    18:42   10:30:29
8   2565    10 May 2014 8:13    19:02   10:49:19    8:13    19:02   10:49:19
9   2565    13 May 2014 8:00    18:58   10:57:42            
10  2565    13 May 2014 8:02    18:58   10:55:30    8:02    18:58   10:55:30
11  2565    14 May 2014 8:31    19:10   10:38:50    8:31    19:10   10:38:50
12  2565    15 May 2014 8:47    18:59   10:12:03    8:47    18:59   10:12:03
13  2565    16 May 2014 9:58    18:49   8:51:22         
14  2565    16 May 2014 9:58    18:49   8:51:15         
15  2565    16 May 2014 9:58    18:49   8:50:48     9:58    18:49   8:50:48
16  2565    17 May 2014 9:07    17:56   8:49:54     9:07    17:56   8:49:54

В ожидаемом результате всякий раз, когда нет времени выхода для соответствующего ввода, продолжительность должна составлять 12:00:00.

Пожалуйста, помогите мне добиться этого. Заранее спасибо.

Комментарии:

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

2. Спасибо @Tanner, теперь входные данные были сокращены.

3. Он сказал несколько … Там еще около 40-50 рядов!

Ответ №1:

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

Что я сделал, так это добавил вашу логику оператора CASE, используемую при вычислении поля OutTime, к строкам, используемым для создания поля Duration. Перенос этого вычисления в функцию ISNULL() означает, что значения со слишком большой продолжительностью вернут все, что вы хотите.

 SELECT t.EmployeeId,
       t.AttendanceDate,
       t.InTime,
       CASE 
            WHEN DATEDIFF(HOUR, t.InTime, t.OutTime) > 23 THEN NULL
            ELSE t.OutTime
       END  AS OutTime,
       ISNULL(CAST (
         DATEDIFF
          (
            HOUR, 
            t.InTime, 
            CASE 
              WHEN DATEDIFF(HOUR, t.InTime, t.OutTime) > 23 
              THEN NULL
              ELSE t.OutTime
            END) AS VARCHAR) 
          ':' 
          CAST(
          DATEDIFF
           (
             minute, 
             t.InTime, 
             CASE 
              WHEN DATEDIFF(HOUR, t.InTime, t.OutTime) > 23 
              THEN NULL
              ELSE t.OutTime
            END
           ) %60  AS VARCHAR), '12:00') AS Duration
FROM   (
           SELECT i.EmployeeId,
                  CONVERT(VARCHAR, i.LogDate, 106) AS AttendanceDate,
                  i.LogDate  AS InTime,
                  (
                      SELECT MIN(o.LogDate) AS OutTime
                      FROM   tmpAccessLogTbl o
                      WHERE  o.Direction = 'Out'
                             AND o.EmployeeId = i.EmployeeId
                             AND o.LogDate > i.LogDate
                  )          AS OutTime
           FROM   tmpAccessLogTbl       i
           WHERE  i.Direction = 'In'
       )    AS t
ORDER BY
       t.EmployeeId,
       t.AttendanceDate

Скрипка

Метки: