У меня есть большая таблица клиентов и их политик, которые настроены следующим образом (фактические даты могут быть в течение года):
Я использовал базовый SQL-запрос, чтобы получить все активные учетные записи, выбрав записи со статусом «Активно» и выдав список активных политик, например:
SELECT * FROM Customers WHERE Status = 'Active'
Однако меня попросили теперь предоставить такой результат:
Конкретный запрос состоит в том, чтобы рассчитать столбец OriginalEffectiveDate на основе дат начала и окончания полиса с использованием следующих критериев: Используйте самую раннюю дату начала полиса в качестве OriginalEffectiveDate клиента, если не было перерыва в покрытии на один месяц или больше. Если был пробел (или пробелы), вместо этого используйте первую дату после последнего пробела.
Таким образом, первоначальной датой вступления Джейн в силу будет 20060101 год, поскольку именно тогда она впервые получила страховое покрытие, и у нее не было перерывов в страховом покрытии. Однако первоначальной датой вступления Джо в силу будет 20200101 год, поскольку у него был перерыв (один месяц или больше) после его первоначального полиса, и это первая дата после последнего перерыва.
Я пытался возиться с операторами CASE, MIN, MAX и TOP, но безрезультатно. Я понятия не имею, как действовать дальше. Я верю в «обучение кого-то ловить рыбу», поэтому, пожалуйста, не стесняйтесь указать мне направление учебника. Камнем преткновения являются части «один месяц или больше» и «после последнего разрыва». Я попытался найти решение в Интернете и не нашел ничего подобного, но, возможно, я не знаю, как правильно сформулировать вопрос.
Одно из возможных решений, которое вы можете попробовать, заключается в следующем, используя отставание.
Сначала получите разрыв в месяцах между текущим 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
В вашем вопросе нет упоминания о какой-либо конкретной платформе, кроме помеченного SQL Server? Естественно, вы получаете решение, на 100 % совместимое с SQL-сервером!
Альтернативой может быть сначала создание временной таблицы для замены CTE, поэтому minrecord
CTE будет вставлен во временную таблицу, select CustomerId, Max(Record) into #temp from (gaps CTE here) etc
, выбор из gaps
CTE в качестве производной таблицы, а затем ссылка на временную таблицу в основном запросе.
Прошу прощения за отсутствие информации. Я попытался запустить команду, чтобы получить версию SQL, но, похоже, она тоже заблокирована. Из-за деликатного характера моей работы я не знаю, как много я могу рассказать об окружающей среде, поэтому я ошибаюсь из-за осторожности. Поскольку я унаследовал эту работу от кого-то, кто больше не работает в компании, я пришел вслепую и, к сожалению, не знаю обо всех ограничениях. Я попробую ваше предложение, чтобы избавиться от предложений WITH.
Вот что у меня в итоге получилось:
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'
Вероятно, это не самый элегантный код, но, похоже, он работает.
Это отлично работало на демо-сайте Fiddle. Однако оказалось, что проприетарная онлайн-платформа, которую я должен использовать для выполнения этого запроса, не поддерживает "WITH", так как выдает следующую ошибку: WITH не является распознанной командой. Я попытался переместить выбранные части туда, где используются имена WITH, и это, похоже, не работает.