Блог

Главная - Вопросы по программированию - SQL — разница между четными / нечетными столбцами в другой столбец

SQL — разница между четными / нечетными столбцами в другой столбец

#sql #sql-server #date #datediff #difference

#sql #sql-сервер #Дата #datediff #разница

Вопрос:

У меня есть таблица, которая состоит из следующих данных.

  Employee      TimeRegister1           TimeRegister2           TimeRegister3           TimeRegister4           
    77    2014-04-01 11:51:43.000 2014-04-01 14:03:52.000 2014-04-01 14:17:01.000 2014-04-01 16:01:12.000
    77    2014-04-03 09:48:33.000 2014-04-03 12:13:43.000 2014-04-03 12:22:27.000 2014-04-03 14:03:43.000
    181   2014-04-02 08:24:34.000 2014-04-02 13:01:10.000 2014-04-02 14:30:31.000 2014-04-02 18:04:04.000

Что мне нужно, так это записать в другом столбце общее количество, рассчитанное на основе различий между каждой парой столбцов (нечетное минус четное) для каждого сотрудника / день.

В приведенном выше примере, для Employee 77 and 2014-04-01 , он должен записать в другой столбец сумму разностей между TimeRegister 2 - TimeRegister 1 , TimeRegister 4 - TimeRegister 3 .

Должно быть выведено что-то вроде этого (секунды не имеют значения, просто нужны часы / минуты):

  Employee      TimeRegister1           TimeRegister2           TimeRegister3           TimeRegister4              CALCULATEDCOL        
    77      2014-04-01 11:51:43.000 2014-04-01 14:03:52.000 2014-04-01 14:17:01.000 2014-04-01 16:01:12.000    2014-04-01 03:56:00.000

Кроме того TimeRegister , столбцов может быть до 30 (я только что показал 4 столбца, но может быть и больше), поэтому мне нужно последовательное вычисление для каждой четной / нечетной пары, пока не закончатся столбцы.

Буду признателен за любую помощь, как я могу добиться этого в SQL. Спасибо.

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

1. Есть ли столбец, который указывает «In» против «Out» (т. Е. В настоящее время вы предполагаете, что существует четное количество записей и направление регистра). Кроме того, «DateTime» — это действительно просто разница во времени, т. Е. Должен быть столбец с другим типом данных, например Time?

2. Привет, Стюарт, спасибо за ваш вклад. Столбец «TimeRegister1» — это столбец «IN» (первый регистр времени за день — всегда IN). Иногда возможно, что нет даже регистра времени (например, сотрудник зарегистрировался, но забыл проверить). Конечно, эти ошибки следует игнорировать. Допустим, сотрудник выписывается на обед, когда он возвращается, он забыл зарегистрироваться. Когда он проверяет в конце дня, это будет рассматриваться как РЕГИСТРАЦИЯ. Аномалии будут обработаны вручную (т. Е. Добавление забытых возвратов / исходов) и соответствующим образом пересчитаны.

3. Это действительно плохой дизайн таблицы. Похоже, что он был разработан на основе физической формы, а не путем размышления о данных. В идеале все эти значения даты и времени должны быть в одном столбце — с отдельной строкой для каждой записи. Вы могли бы приблизиться к этому, выполнив UNPIVOT сначала для этих данных.

4. Привет, Дэмиен, в исходной таблице действительно есть значения даты и времени в одном столбце с отдельной строкой для каждой записи. Это таблица, полученная в результате операции PIVOT. Мы сделали это, чтобы передать его в программную сетку, чтобы сотрудники отдела кадров могли работать с входами и выходами в одной строке для лучшего анализа. Конечно, я могу работать с исходной таблицей, а затем СВОДИТЬ все, когда будут выполнены все вычисления. Есть мысли?

Ответ №1:

Предполагая, что представленная таблица верна (т. Е. Не 30 столбцов), вы можете сделать это, введя DATEDIFF DateTimes, сложив различия вместе, а затем добавив их обратно к базовой дате одного из периодов времени (при условии, что все даты и даты приходятся на один и тот же день). Вы можете исключить секунды, выбрав минуты в качестве разрешения diff / add. И для обработки отсутствующих данных вам потребуется ISNULL() или COALESCE для каждого In / Out сопряжения.

 SELECT Employee, TimeRegister1, TimeRegister2, TimeRegister3, TimeRegister4, 
       DATEADD(mi, ISNULL(DATEDIFF(mi, TimeRegister1, TimeRegister2), 0) 
                   ISNULL(DATEDIFF(mi, TimeRegister3, TimeRegister4), 0),
               CAST(CAST(TimeRegister1 AS DATE) AS DATETIME)) 
       as CALCULATEDCOL        
FROM TimeRegister;

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

SQLFiddle здесь

Если у вас действительно есть до 30 столбцов, вам нужно будет повторить вычисление для каждой пары In/Out данных (и надеяться, что сотрудники не покидают здание чаще 15 раз в день!).

Редактировать
Чтобы сделать это из необработанной таблицы данных ввода / вывода часов, предполагая, что направление доступно (в противном случае данные практически бесполезны), и отбрасывая любые данные, в которых сотрудник не включал и не выключал последовательно, в тот же день, как насчет:

 WITH cte AS 
(
   SELECT Employee, 
          TimeRegister,
          Direction,
          CAST(TimeRegister AS DATE) AS TheDate,
          ROW_NUMBER() OVER (PARTITION BY Employee, CAST(TimeRegister AS DATE) 
                             ORDER BY TimeRegister ASC) AS Rnk
   FROM TimeRegister
)
SELECT 
   cur.Employee,
   DATEADD(mi, SUM(DATEDIFF(mi, cur.TimeRegister, nxt.TimeRegister)), 
           CAST(cur.TheDate AS DATETIME)) AS CALCULATEDCOL
   FROM cte cur
      INNER JOIN cte nxt
      ON cur.Employee = nxt.Employee 
         AND cur.TheDate = nxt.TheDate AND cur.Rnk   1 = nxt.Rnk
   WHERE cur.Direction = 'I' AND nxt.Direction = 'O'
   GROUP BY cur.Employee, cur.TheDate;

SQLFiddle здесь