Рассчитайте дату окончания строк раздела, используя дату начала следующего раздела
Как я могу написать CTE в SQL Server, чтобы получить End_Date для данного раздела строк, где раздел определяется по лицу и типу, при этом новый раздел начинается, если тип изменяется внутри человека. End_Date определяется как Start_Date следующего раздела Type в разделе Person. Последний раздел внутри Person всегда будет иметь нулевое значение End_Date.
Ранее я задавал аналогичный вопрос, на который получил ответ, но я не смог показать случай, когда Тип может не только меняться несколько раз внутри Человека, но также может снова повторять один и тот же Тип. В этом сценарии также следует создать новый раздел, как показано в данных ниже.
Для следующих исходных данных записи должны быть отсортированы по dt_eff для человека.
Источник данных
Person Type dt_eff
123 ABC 2018-10-23
123 DEF 2018-12-19
124 ABC 2020-01-01
124 ABC 2020-02-15
124 ABC 2020-05-14
124 DEF 2020-10-13 < Note change of Type to DEF
124 ABC 2021-01-15 < Note change of Type back to ABC
Ожидаемый результат
Person Type Start_Date End_Date Partition
123 ABC 2018-10-23 2018-12-19 123:1 < Start_Date from 123:DEF
123 DEF 2018-12-19 NULL 123:2
124 ABC 2020-01-01 2020-10-13 124:1
124 ABC 2020-02-15 2020-10-13 124:1
124 ABC 2020-05-14 2020-10-13 124:1 < Start_Date from 124:DEF
124 DEF 2020-10-13 2021-01-15 124:2< Start_Date from next 124:ABC
124 ABC 2021-01-15 NULL 124:3
Обратите внимание, что столбец Partition не является обязательным выводом, он добавлен просто для пояснения.
У меня есть для вас ответ, как только вопрос прояснится.
Я не уверен, какие именно детали вы ищете. Возможно, мне следовало дать более подробную информацию. Но в моем предыдущем вопросе/сообщении конкретно требовалась дата окончания при изменении типа. Если тип тот же, вы не возьмете эту дату. В вашем примере просто измените тип FGD на ABC и повторите попытку. это не даст желаемого результата.
Хорошо, только потому, что мне было скучно и я уже создал ответ, я отредактировал ваш вопрос, чтобы было понятно, о чем спрашивают. Вы должны помнить, что у вас в голове весь контекст вопроса, поэтому все имеет для вас смысл. Но у кого-то другого, кто не сталкивался с проблемой, нет контекста, и поэтому такого краткого объяснения, которое было у вас изначально, недостаточно. В будущем, пожалуйста, постарайтесь сформулировать ясный и точный вопрос, охватывающий необходимые крайние случаи. В противном случае вы продолжите привлекать отрицательные голоса, как этот (и ваш последний вопрос) вопрос.
@ Дейл К, ты прав. Мне следовало добавить комментарии и дополнительные строки, чтобы было понятнее. Спасибо за редактирование вопроса и предоставление запроса. Он работает так, как ожидалось.
Ответы 1
Ключевой вывод здесь заключается в том, что вы не всегда можете получить нужные результаты с помощью одного запроса... Иногда вам придется выполнить несколько шагов. Я использую следующий подход:
- Логически подумайте, как можно решить проблему.
- Создайте запрос, который дает логический результат.
- Посмотрите, есть ли возможность упростить запрос.
Следующее дает желаемые результаты... как это работает, объясняется в комментариях.
with cte1 as (
select *
-- 1. Find the dt_eff value of the next row, we need this to get the end date
, lead(dt_eff) over (partition by Person order by dt_eff) dt_eff_lead
-- Find the Type value of the previous row, we need this to detect a change in type
, lag(Type, 1, Type) over (partition by Person order by dt_eff) type_lag
from Person
), cte2 as (
select Person, Type, dt_eff Start_Date
, dt_eff_lead
-- Count the number of Type transitions by comparing this row with the previous row
-- prior to and including the current row
-- Each time the count changes gives us a new partition to use in the final result
, sum(case when Type <> type_lag then 1 else 0 end)
over (partition by person order by dt_eff asc
rows between unbounded preceding and current row) TypeGroup
from cte1
)
select Person, Type, Start_Date
-- Get the maximum dt_eff_lead for a given type partition within a person partition
, max(dt_eff_lead) over (partition by Person, TypeGroup) End_Date
from cte2
order by Person, Start_Date, Type;
Скрипка (с использованием слегка измененных данных из вашего последнего вопроса)
«Предупреждение: нулевое значение удаляется агрегатной или другой операцией SET». Эта проблема возникает только в том случае, если тип один и тот же для последних записей. Если тип отличается, то он заполняется правильно.
@skv это всего лишь предупреждение и обычно не является проблемой.
вам нужно приложить немного больше усилий, чтобы объяснить свой вопрос, потратив время на объяснение логики словами, а также на отображение данных. И в этом случае важно объяснить, почему ответ на ваш предыдущий вопрос не дает желаемых результатов. Если вы это сделаете, я могу попытаться снова открыть для вас вопрос.