Получить первое свидание ИЛИ первое свидание после разрыва

У меня есть большая таблица клиентов и их политик, которые настроены следующим образом (фактические даты могут быть в течение года):

Записывать Пользовательский ИД Имя ПолитикаСтарт ПолитикаКонец Агент Положение дел 1 12345 Джейн Доу 20060101 20081231 Джон Смит Срок 2 12345 Джейн Доу 20090101 20101231 Сара По Срок 3 12345 Джейн Доу 20110101 Сара По Активный 4 12346 Джо Грязь 20130101 20141231 Блейк Ли Срок 5 12346 Джо Грязь 20200101 Блейк Ли Активный

Я использовал базовый SQL-запрос, чтобы получить все активные учетные записи, выбрав записи со статусом «Активно» и выдав список активных политик, например:

SELECT * FROM Customers WHERE Status = 'Active'
Записывать Пользовательский ИД Имя ПолитикаСтарт ПолитикаКонец Агент Положение дел 3 12345 Джейн Доу 20110101 Сара По Активный 5 12346 Джо Грязь 20200101 Блейк Ли Активный

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

Записывать Пользовательский ИД Имя ИсходнаяДата вступления в силу Агент Положение дел 3 12345 Джейн Доу 20060101 Сара По Активный 5 12346 Джо Грязь 20200101 Блейк Ли Активный

Конкретный запрос состоит в том, чтобы рассчитать столбец OriginalEffectiveDate на основе дат начала и окончания полиса с использованием следующих критериев: Используйте самую раннюю дату начала полиса в качестве OriginalEffectiveDate клиента, если не было перерыва в покрытии на один месяц или больше. Если был пробел (или пробелы), вместо этого используйте первую дату после последнего пробела.

Таким образом, первоначальной датой вступления Джейн в силу будет 20060101 год, поскольку именно тогда она впервые получила страховое покрытие, и у нее не было перерывов в страховом покрытии. Однако первоначальной датой вступления Джо в силу будет 20200101 год, поскольку у него был перерыв (один месяц или больше) после его первоначального полиса, и это первая дата после последнего перерыва.

Я пытался возиться с операторами CASE, MIN, MAX и TOP, но безрезультатно. Я понятия не имею, как действовать дальше. Я верю в «обучение кого-то ловить рыбу», поэтому, пожалуйста, не стесняйтесь указать мне направление учебника. Камнем преткновения являются части «один месяц или больше» и «после последнего разрыва». Я попытался найти решение в Интернете и не нашел ничего подобного, но, возможно, я не знаю, как правильно сформулировать вопрос.

ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
3
0
88
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

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

Сначала получите разрыв в месяцах между текущим PolicyEnd и следующим PolicyStart, затем можно найти самую последнюю запись для каждого клиента, где разрыв превышает 1 месяц.

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

with gaps as (
  select *,
    case when DateDiff(month, PolicyEnd,Lead(PolicyStart) over(partition by CustomerID order by Record)) > 1 then 1 else 0 end b
  from t
), minrecord as (
  select CustomerID, Max(Record) mr
  from gaps
  where b = 1
  group by CustomerID
)
select Record, CustomerID, [Name], Agent, [Status], ed.OriginialEffectiveDate
from t
cross apply (
  select Min(PolicyStart) OriginialEffectiveDate
  from t t2
  where t2.CustomerID = t.CustomerID and
    t2.Record > IsNull((select mr from minrecord where CustomerID = t2.CustomerID), 0)
)ed
where t.[Status] = 'active';

Посмотрите эту демо Fiddle

Это отлично работало на демо-сайте Fiddle. Однако оказалось, что проприетарная онлайн-платформа, которую я должен использовать для выполнения этого запроса, не поддерживает "WITH", так как выдает следующую ошибку: WITH не является распознанной командой. Я попытался переместить выбранные части туда, где используются имена WITH, и это, похоже, не работает.

user2597747 10.01.2023 22:38

В вашем вопросе нет упоминания о какой-либо конкретной платформе, кроме помеченного SQL Server? Естественно, вы получаете решение, на 100 % совместимое с SQL-сервером!

Stu 10.01.2023 23:01

Альтернативой может быть сначала создание временной таблицы для замены CTE, поэтому minrecord CTE будет вставлен во временную таблицу, select CustomerId, Max(Record) into #temp from (gaps CTE here) etc, выбор из gaps CTE в качестве производной таблицы, а затем ссылка на временную таблицу в основном запросе.

Stu 10.01.2023 23:01

Прошу прощения за отсутствие информации. Я попытался запустить команду, чтобы получить версию SQL, но, похоже, она тоже заблокирована. Из-за деликатного характера моей работы я не знаю, как много я могу рассказать об окружающей среде, поэтому я ошибаюсь из-за осторожности. Поскольку я унаследовал эту работу от кого-то, кто больше не работает в компании, я пришел вслепую и, к сожалению, не знаю обо всех ограничениях. Я попробую ваше предложение, чтобы избавиться от предложений WITH.

user2597747 11.01.2023 02:06
Ответ принят как подходящий

Вот что у меня в итоге получилось:

SELECT Record
    ,Customers.CustomerID
    ,Name
    ,CustomerEffectiveDate
    ,Agent
    ,Status
FROM Customers
JOIN (
    SELECT CustomerID
        ,MIN(PolicyStart) AS CustomerEffectiveDate
    FROM (
        SELECT CustomerID
            ,PolicyStart
            ,DateDiff(month, PolicyEnd, Lead(PolicyStart) OVER (
                    PARTITION BY CustomerID ORDER BY CustomerID
                        ,PolicyStart
                    )) AS gapMonths
        FROM Customers
        ) gaps
    WHERE gapMonths < 2
        OR gapMonths IS NULL
    GROUP BY CustomerID
    ) minstart ON customers.CustomerID = minstart.CustomerID
WHERE status = 'Active'

Вероятно, это не самый элегантный код, но, похоже, он работает.

Другие вопросы по теме