Блог

У меня есть таблица в SQL Server, подобная этой:

  ---------- ----------- ------------ 
| DateFrom | Completed | EmployeeID |
 ---------- ----------- ------------ 
DateFrom: date not null -- unique for each EmployeeID
Completed: bit not null
EmployeeID: bigint not null
 

• Каждая строка принадлежит подпериоду, определяемому начальной датой, и может быть завершена или нет.
• У каждого сотрудника может быть несколько подпериодов.
• Период определяется списком упорядоченных подпериодов до завершения последнего подпериода.

Я хочу создать представление, которое вернет дату начала последнего периода для каждого идентификатора сотрудника следующим образом:

1. Если значение true не завершено, получите минимальную дату из. [У сотрудника есть один период, который все еще не завершен]

  ---------- ----------- ------------ 
| DateFrom | Completed | EmployeeID |
 ---------- ----------- ------------ 
|2021-01-01|   false   |     1      |
|2021-01-05|   false   |     1      |
|2021-01-09|   false   |     1      |
|2021-01-10|   false   |     1      |
|2021-01-07|   false   |     2      |
|2021-01-15|   false   |     2      |
 ---------- ----------- ------------ 

Expected Result:
2021-01-01 for EmployeeID = 1
2021-01-07 for EmployeeID = 2
 

2. В противном случае верните минимальную дату после того, как последнее завершенное значение равно true. [Последний период все еще не завершен]

  ---------- ----------- ------------ 
| DateFrom | Completed | EmployeeID |
 ---------- ----------- ------------ 
|2021-01-01|   false   |     1      |
|2021-01-05|   true    |     1      |
|2021-01-09|   false   |     1      |
|2021-01-10|   false   |     1      |
|2021-01-07|   true    |     2      |
|2021-01-15|   false   |     2      |
 ---------- ----------- ------------ 

Expected Result:
2021-01-09 for EmployeeID = 1
2021-01-15 for EmployeeID = 2
 

3. Если максимальная дата завершения = true, верните минимальную дату перед последним завершенным значением true и после значения true перед ним, если оно существует. [Последний период завершается несколькими подпериодами]

  ---------- ----------- ------------ 
| DateFrom | Completed | EmployeeID |
 ---------- ----------- ------------ 
|2021-01-01|   false   |     1      |
|2021-01-05|   true    |     1      |
|2021-01-09|   false   |     1      |
|2021-01-10|   true    |     1      |
|2021-01-07|   false   |     2      |
|2021-01-15|   true    |     2      |
 ---------- ----------- ------------ 

Expected Result:
2021-01-09 for EmployeeID = 1
2021-01-07 for EmployeeID = 2
 

4. Если максимальная дата из завершена = true и нет других строк или строки до ее завершения = true, верните максимальную дату из. [Последний период завершается одним подпериодом]

  ---------- ----------- ------------ 
| DateFrom | Completed | EmployeeID |
 ---------- ----------- ------------ 
|2021-01-01|   false   |     1      |
|2021-01-05|   false   |     1      |
|2021-01-09|   true    |     1      |
|2021-01-10|   true    |     1      |
|2021-01-07|   true    |     2      |
 ---------- ----------- ------------ 

Expected Result:
2021-01-10 for EmployeeID = 1
2021-01-07 for EmployeeID = 2
 

Я ищу наиболее оптимизированное решение.

Я пробовал это, но в третьем примере я получаю нулевое значение:

 WITH T AS (
    SELECT EmployeeID
        , MAX(CASE WHEN Completed = 0 THEN NULL ELSE DateFrom END) MaxDateFrom 
    FROM TableDates
    GROUP BY EmployeeID
)
SELECT TableDates.EmployeeID, MIN(TableDates.DateFrom) DateFrom
FROM T
LEFT JOIN TableDates ON T.EmployeeID = TableDates.EmployeeID
    AND (T.MaxDateFrom IS NULL OR TableDates.DateFrom > T.MaxDateFrom)
GROUP BY TableDates.EmployeeID
 

Я думаю, вам просто нужна условная агрегация — с кучей логики. Предполагая, что у вас есть строки на каждый день, я думаю, это делает то, что вы хотите:

 select employeeid,
       (case when -- case 4
                  min(completed) = max(completed) and
                  min(completed) = 'true'
             then max(datefrom) 
             when -- case 1
                  min(completed) = max(completed) and
                  min(completed) = 'false'
             then min(datefrom) 
             when -- case 3
                  max(datefrom) = max(case when completed = 'true' then datefrom end)
             then min(case when completed_seqnum = 1 then datefrom end)
             else dateadd(day, 1, max(case when completed = 'true' then datefrom end))
        end)
from (select t.*,
             sum(case when completed = 'true' then 1 else 0 end) over (partition by employeeid order by datefrom desc) as completed_seqnum
      from t
     ) t
group by employeeid;
 

Необходимость в строке каждый день на самом деле просто удобна — например, позволяет коду добавлять один день, чтобы получить дату после определенного значения «true» false. Это также может быть выполнено с помощью lead() подзапроса.

Примечание: это не обрабатывает все условия (по крайней мере, с ненулевой датой. Например, он возвращается NULL , когда в конце данных есть последовательность «true».

Если это проблема — эта версия вашего вопроса была задана. Задайте новый вопрос с соответствующими образцами данных и желаемыми результатами. Я также думаю, что вы могли бы объяснить проблему, которую пытаетесь решить, и упростить объяснение.

Редактировать:

Если даты отсутствуют, вы можете использовать:

 select employeeid,
       (case when -- case 4
                  min(completed) = max(completed) and
                  min(completed) = 'true'
             then max(datefrom) 
             when -- case 1
                  min(completed) = max(completed) and
                  min(completed) = 'false'
             then min(datefrom) 
             when -- case 3
                  max(datefrom) = max(case when completed = 'true' then datefrom end)
             then min(case when completed_seqnum = 1 then datefrom end)
             else max(case when completed = 'true' then next_datefrom end)
        end)
from (select t.*,
             lead(datefrom) over (partition by employeeid order by datefrom) as next_datefrom,
             sum(case when completed = 'true' then 1 else 0 end) over (partition by employeeid order by datefrom desc) as completed_seqnum
      from t
     ) t
group by employeeid;
 

Вот рабочий запрос. Вероятно, это слишком сложно, но я оставляю упрощение вам.

Он обрабатывает 3 обращения, все из которых разделены по EmployeeID в соответствии с запросом, следующим образом:

1. Когда не Completed=1 существует, определяется с помощью sum(Completed) over() , затем first_value(DateFrom) используется.
2. Когда значение последней строки completed=1 равно, а предыдущая строка равна completed=0 , определяется с помощью last_value(Completed) , а lag(Completed) затем max(case when Completed = 0 then DateFrom else null end) используется.
3. Сложный случай, когда a Completed=1 существует и не является последним. В этом случае найдена дата из самой последней строки where Completed=1 , а затем найдена min(DateFrom) для всех строк, более поздних, чем ранее обнаруженная строка, до предыдущей Completed=1 .
4. Если последняя строка имеет completed=1 и предпоследняя строка имеет completed=1 , тогда используйте DateFrom из последней строки. Coalesce гарантирует это, если все остальные параметры равны нулю.

 insert into @Test (EmployeeId, DateFrom, Completed)
values
-- Scenario 1
(1, '2021-01-01', 0),
(1, '2021-01-02', 0),
(1, '2021-01-03', 0),
-- Scenario 2
(2, '2021-01-01', 0),
(2, '2021-01-02', 1),
(2, '2021-01-03', 0),
(2, '2021-01-04', 0),
-- Scenario 3
(3, '2021-01-01', 0),
(3, '2021-01-02', 1),
(3, '2021-01-03', 0),
(3, '2021-01-04', 1),
-- Special case, single row
(4, '2021-01-01', 1),
-- Scenario 4
(5, '2021-01-01', 0),
(5, '2021-01-02', 0),
(5, '2021-01-03', 1);

with cte as (
  select *
    -- First value of DateFrom over all rows (not the default)
    , first_value (DateFrom) over (partition by EmployeeId order by DateFrom asc rows between unbounded preceding and unbounded following) FirstDateFrom
    -- Last value of Completed over all rows (not the default)
    , last_value (Completed) over (partition by EmployeeId order by DateFrom asc rows between unbounded preceding and unbounded following) LastCompleted
    -- Find the Date of the last row with Completed = 1
    , max (case when Completed = 1 then DateFrom else null end) over (partition by EmployeeId order by DateFrom asc rows between unbounded preceding and unbounded following) LastCompletedNew
    -- Regular row number
    , row_number() over (partition by EmployeeId order by DateFrom desc) RowNumber
    -- Total number of rows with Completed = 1
    , sum(convert(int,Completed)) over (partition by EmployeeId) SumOfCompleted
    -- Max value of DateFrom where Completed = 0
    , max(case when Completed = 0 then DateFrom else null end) over (partition by EmployeeId order by DateFrom asc rows between unbounded preceding and unbounded following) MaxDateFrom
    -- Check the lagged complete to see if the last 2 rows are completed = 1
    , lag(Completed) over (partition by EmployeeId order by DateFrom asc) LaggedComplete
    -- Borrowed from Gordon to check which rows are prior to the last Completed = 1 and before the preceding Completed = 1
    , sum(case when completed = 1 then 1 else 0 end) over (partition by employeeid order by datefrom desc) as completed_seqnum
  from @Test
)
select
  EmployeeId
  -- Use the only DateFrom if there is only one
  , coalesce(case
    -- Scenario 1
    when SumOfCompleted = 0 then FirstDateFrom
    when LastCompleted = 1 then
      case
      -- Scenario 4
      when coalesce(LaggedComplete,0) = 1 then DateFrom
      -- Scenario 3
      else Scenario3
      end
    -- Scenario 2
    else ActualResult
    end, DateFrom) FinalResult
  --, * -- Uncomment for working
from (
  select *
    -- Find the lowest DateFrom which is greater then the DateFrom of the last row where Completed = 1
    , min(case when DateFrom > LastCompletedNew then DateFrom else null end) over (partition by EmployeeId) ActualResult
    -- Find the min DateFrom over the rows between the last Completed=1 and the Completed=1 before it (if it exists)
    , min(case when completed_seqnum = 1 then DateFrom else null end) over (partition by EmployeeId order by DateFrom asc rows between unbounded preceding and unbounded following) Scenario3
  from cte
) x
-- Because we have calculated the same result for every row we just take the first
where RowNumber = 1
order by x.EmployeeId asc, x.DateFrom asc;
 

Примечание: Предполагается, что на дату приходится только одна строка.