Блог

У меня есть таблица, состоящая из информации о времени. Это в основном:

Сотрудник, дата, последовательность, время поступления, время ожидания.

Они могут отсчитывать время несколько раз в день, поэтому я пытаюсь получить все отсчеты за день в одной строке. Мой результат будет примерно таким:

Сотрудник, дата, TimeIn1, TimeOut1, TimeIn2, TimeOut2, TimeIn3, TimeOut3….

Где 1, 2 и 3 являются порядковыми номерами. Я знаю, что мог бы просто выполнить кучу левых соединений с самой таблицей на основе employee=employee, date= date и seq= seq 1, но есть ли способ сделать это в сводной таблице? Я не хочу сводить поля employee и date, только время и время ожидания.

Короткий ответ: да, это возможно.

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

_{Извините за стену кода; ни один из скриптовых сайтов не работает с моего текущего компьютера}

 
declare @test table (
    pk int,
    workdate date,
    seq int,
    tIN time,
    tOUT time
)

insert into @test values 
(1, '2020-11-25', 1, '08:00', null),
(1, '2020-11-25', 2, null, '11:00'),
(1, '2020-11-25', 3, '11:32', null),
(1, '2020-11-25', 4, null, '17:00'),
(2, '2020-11-25', 5, '08:00', null),
(2, '2020-11-25', 6, null, '09:00'),
(2, '2020-11-25', 7, '09:15', null),
-- new date
(1, '2020-11-27', 8, '08:00', null),
(1, '2020-11-27', 9, null, '08:22'),
(1, '2020-11-27', 10, '09:14', null),
(1, '2020-11-27', 11, null, '12:08'),
(1, '2020-11-27', 12, '01:08', null),
(1, '2020-11-27', 13, null, '14:40'),
(1, '2020-11-27', 14, '14:55', null),
(1, '2020-11-27', 15, null, '17:00')
 
select * 
from (

  /* this just sets the column header names and condenses their values */
  select 
    pk,
    workdate,
    colName = case when tin is not null then 'TimeIn'   cast(empDaySEQ as varchar) else 'TimeOut'   cast(empDaySEQ as varchar) end,
    colValue = coalesce(tin, tout)
  from (

      /* main query */
      select 
        pk,
        workdate, 
        /* grab what pair # this clock in or out is; reset by employee amp; date */
        empDaySEQ = (row_number() over (partition by pk, workdate order by seq) / 2)   (row_number() over (partition by pk, workdate order by seq) % 2),
        tin,
        tout
      from @test
  ) i
) a 
PIVOT (
 max(colValue) 
 for colName
 IN ( /* replace w/ dynamic if you don't know upper boundary of max in/out pairs */
   [TimeIn1],
   [TimeOut1],
   [TimeIn2],
   [TimeOut2],
   [TimeIn3],
   [TimeOut3],
   [TimeIn4],
   [TimeOut4]
 )
) mypivotTable 


 

генерирует эти результаты.

(Я бы предоставил демонстрационную версию скрипки, но сегодня они у меня не работают)