Отображение дней между статусами, перечисленными горизонтально

avatar
redviper2100
8 апреля 2018 в 11:12
44
2
1

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

Это необработанная таблица:

Case_number Operation_Name  Date
112345      Canceled        22.04.2018
112345      On hold         20.04.2018
112345      On hold         15.04.2018
112345      Processing      10.04.2018
112345      Open            08.04.2018
112347      Closed          21.04.2018
112347      On hold         20.04.2018
112347      On hold         18.04.2018
112347      Processing      15.04.2018
112347      Processing      13.04.2018
112347      Open            11.04.2018

Вывод должен выглядеть так

enter image description here

Как видите:

112347      On hold         20.04.2018
112347      On hold         18.04.2018

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

Я пытался использовать функцию лида, но безуспешно.

Заранее спасибо за ваше время и идеи, ребята!

Источник
dnoeth
8 апреля 2018 в 11:35
0

Возможно ли иметь один и тот же статус несколько раз с другими значениями между ними, например. Processing - On hold - Processing?

redviper2100
8 апреля 2018 в 11:44
0

Да, это возможно, и в этом случае два статуса «обработка» (как показано в вашем примере) учитываются как разные статусы.

Ответы (2)

avatar
dnoeth
8 апреля 2018 в 12:20
1

Это удалит дополнительные строки с таким же статусом и назначит порядковый номер:

with cte as 
 (
   select *,
      lead(Operation_Name) -- previous Operation_Name
      over (partition by case_number
            order by Dat desc) as prev_op
   from tab
 )
select cte.*, 
   datediff(day
           ,date
           ,lag(date) -- next date
            over (partition by case_number
                  order by Date desc)
           ) as days_between,
   row_number()
   over (partition by case_number
         order by Date desc) as rn
from cte
where prev_op <> Operation_Name -- different value
   or prev_op is null           -- or first row
;

Теперь вы можете передать это в PIVOT или сделать max(case):

with cte as 
 (
   select *,
      lead(Operation_Name) -- previous Operation_Name
      over (partition by case_number
            order by Date desc) as prev_op
   from tab
 )
, cte2 as
 (
   select cte.*, 
      datediff(day
              ,date
              ,lag(date) -- previous Operation_Name
               over (partition by case_number
                     order by Date desc)
) as days_between,
      row_number()
      over (partition by case_number
            order by Date desc) as rn
   from cte
   where prev_op <> Operation_Name -- different value
      or prev_op is null           -- or first row
 )
select case_number,
   max(case when rn = 1 then Operation_Name end),
   max(case when rn = 2 then Operation_Name end),
   max(case when rn = 2 then days_between end),
   max(case when rn = 3 then Operation_Name end),
   max(case when rn = 3 then days_between end),
   max(case when rn = 4 then Operation_Name end),
   max(case when rn = 4 then days_between end)
from cte2
group by case_number
avatar
RnP
8 апреля 2018 в 12:17
1

Это можно сделать как прямой SQL-запрос, но потребуется немного усилий.

В принципе, нам нужно 1. Установите порядок строк для каждого case_number и 2. Выполните двойную сводную операцию, чтобы получить столбец Operation_Name и столбец Days_Elapsed для каждого столбца по шагу

.

См. ниже решение, в котором обрабатывается до 4 шагов. Это тривиально изменить, чтобы добавить больше шагов, если вам нужно 10. Поэкспериментируйте с CTE и промежуточными результатами, чтобы понять шаги в формулировке этого.

Скрипка SQL

Настройка схемы MS SQL Server 2017:

create table test (id int, status varchar(max), date datetime)

insert test values 
(1, 'cancelled', '2018-04-07'),
(1, 'hold', '2018-04-05'),
(1, 'processing', '2018-04-04'),
(1, 'processing', '2018-04-02'),
(2, 'processing', '2018-04-01'),
(2, 'cancelled', '2018-04-07')

Запрос 1:

;WITH Ordered AS (
  SELECT id, status, date,
    ROW_NUMBER() OVER(PARTITION BY id ORDER BY date desc) AS rowNumber,
    LEAD(date) OVER(PARTITION BY id ORDER BY date desc) AS prevDate
  FROM test
) 
SELECT 
    id, 
    MAX([1]) AS Operation_Latest,
    MAX([D1]) AS DaysElapsed_Prev1_Latest,
    MAX([2]) AS Operation_Prev1, 
    MAX([D2]) AS DaysElapsed_Prev2_Prev1,
    MAX([3]) AS Operation_Prev2, 
    MAX([D3]) AS DaysElapsed_Prev3_Prev2,
    MAX([4]) AS Operation_Prev3,
    MAX([D4]) AS DaysElapsed_Prev4_Prev3
 FROM 
 (SELECT id, rowNumber, status, 
      DATEDIFF(day, prevDate, date) AS daysSinceLast,
      'D' + CONVERT(varchar, rowNumber) AS DaysPivot
  FROM Ordered) AS p
 PIVOT
 (
   MAX(status) FOR rowNumber IN ([1], [2], [3], [4])
 ) AS pv1
 PIVOT
 (
   MAX(daysSinceLast) FOR daysPivot IN ([D1], [D2], [D3], [D4])
 ) AS pv2
GROUP BY id

Результаты:

| id | Operation_Latest | DaysElapsed_Prev1_Latest | Operation_Prev1 | DaysElapsed_Prev2_Prev1 | Operation_Prev2 | DaysElapsed_Prev3_Prev2 | Operation_Prev3 | DaysElapsed_Prev4_Prev3 |
|----|------------------|--------------------------|-----------------|-------------------------|-----------------|-------------------------|-----------------|-------------------------|
|  1 |        cancelled |                        2 |            hold |                       1 |      processing |                       2 |      processing |                  (null) |
|  2 |        cancelled |                        6 |      processing |                  (null) |          (null) |                  (null) |          (null) |                  (null) |