Пометить записи через 7 дней после ранее идентифицированной записи
Я хочу пометить те строки, которые находятся на расстоянии >= 7 дней от ранее идентифицированной записи. В примере ниже допустимыми строками являются 4-13-2022, 4-20-2022 и 4-28-2022.
drop table #tmp1
create table #tmp1(
membernum int,
sevrfromdate date
)
insert into #tmp1
values (1000016, '4/13/2022')
insert into #tmp1
values (1000016, '4/15/2022')
insert into #tmp1
values (1000016, '4/20/2022')
insert into #tmp1
values (1000016, '4/22/2022')
insert into #tmp1
values (1000016, '4/23/2022')
insert into #tmp1
values (1000016, '4/28/2022')
Это моя неудачная попытка отметить рекорды.
select *,
case when datediff(d,coalesce(lag(sevrfromdate) over (partition by membernum order by sevrfromdate),
dateadd(d, -7, sevrfromdate)), sevrfromdate) > = 5 then 1 else 0 end as lasttm,
lag(sevrfromdate) over (partition by membernum order by sevrfromdate)
from #tmp1
Обратите внимание на добавленное мной форматирование кода, которое вы удалили при редактировании.
Не могли бы вы объяснить, как должна работать ваша логика? (непонятно, что вы подразумеваете под «ранее идентифицировать запись»); ваш предпринятый запрос помечает каждую строку как 1, если ее дата подачи более чем на 4 дня позже даты подачи предыдущей строки (лаг). Я не понимаю, как вы отметили свои строки цифрой 1.
Что такое «идентификационная запись»?
минимально воспроизводимый пример должен включать в себя как данные таблицы образцов, так и ожидаемый результат.
Обратите внимание, что в таблицах есть строки, а не записи.
У меня такое ощущение, что вам нужен способ рекурсивно получать строки, находящиеся на расстоянии >= 7 дней от ранее определенной строки, в этом случае LAG нельзя использовать, и вам, вероятно, понадобится CTE.
«Идентифицированная запись» будет следующей записью >=7 дней. В приведенном выше примере следующей идентифицированной записью будет 28 апреля 2022 г.
28 - 23 = 5, что составляет менее 7 дней.
Сравнение будет проводиться не с 23 апреля 2022 г., а с ранее определенной строкой для 20 апреля 2022 г. Итак, с 20 апреля 22 года следующая действительная строка — 28 апреля 2022 года.
Ответы 2
WITH CTE AS (
SELECT
membernum,
sevrfromdate,
ROW_NUMBER() OVER (PARTITION BY membernum ORDER BY sevrfromdate) AS rn
FROM #tmp1
),
FlaggedRecords AS (
SELECT
c1.membernum,
c1.sevrfromdate,
CASE
WHEN c1.rn = 1 THEN 'True'
WHEN DATEDIFF(DAY, c2.sevrfromdate, c1.sevrfromdate) >= 7 THEN 'True'
ELSE 'False'
END AS Is7days
FROM CTE c1
LEFT JOIN CTE c2
ON c1.membernum = c2.membernum
AND c1.rn = c2.rn + 1
),
TrackingCTE AS (
SELECT
membernum,
sevrfromdate,
Is7days,
MAX(CASE WHEN Is7days = 'True' THEN sevrfromdate END)
OVER (PARTITION BY membernum ORDER BY sevrfromdate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS LastTrueDate
FROM FlaggedRecords
),
FinalFlagging AS (
SELECT
membernum,
sevrfromdate,
Is7days,
LastTrueDate,
CASE
WHEN Is7days = 'True' THEN 'True'
WHEN DATEDIFF(DAY, LastTrueDate, sevrfromdate) >= 7 THEN 'True'
ELSE 'False'
END AS FinalIs7days
FROM TrackingCTE
)
SELECT membernum, sevrfromdate, FinalIs7days, LastTrueDate
FROM FinalFlagging
ORDER BY sevrfromdate;
Этот запрос даст вам желаемый результат. FiddlerOutput
Цитата
Если я изменю набор записей. код не возвращает правильные флаги. INSERT INTO ##tmp1 (membernum, sevrfromdate) ЗНАЧЕНИЯ (1000016, '2022-04-13'), (1000016, '2022-04-15'), (1000016, '2022-04-20'), (1000016, '22.04.2022'), (1000016, '23.04.2022'), (1000016, '30.04.2022');
В этом случае правильные значения: 2022-04-13, 2022-04-20, 2022-04-30.
Как указал выше @siggemannen, здесь мы можем использовать рекурсивный запрос для выбора первой и следующей помеченной даты.
- Сначала вычислите возможную следующую дату для каждой строки — min(sevrfromdate), где эта дата>=7 дней от текущей даты строки.
- Затем мы берем первую строку (очевидно, помеченную) и рекурсивно берем другие помеченные строки.
- Наконец, присоедините исходную таблицу с помощью рекурсивного запроса.
См. пример
with t as(
select membernum, sevrfromdate
,(select min(sevrfromdate)
from #tmp1 t2 where t2.membernum=t.membernum
and t2.sevrfromdate>=dateadd(d,7,t.sevrfromdate)
) nextdate
from #tmp1 t
)
,r as(
select 0 lvl, membernum, sevrfromdate
, nextdate
from t
where sevrfromdate=(select min(sevrfromdate)
from #tmp1 t2 where t2.membernum=t.membernum)
union all
select lvl+1,t.membernum, t.sevrfromdate
,t.nextdate
from r inner join t on r.membernum=t.membernum
and t.sevrfromdate=r.nextdate
)
select t.membernum, t.sevrfromdate
,case when r.sevrfromdate is not null then 1 else 0 end flag
,r.nextdate as next_date
from #tmp1 t
left join r on r.membernum=t.membernum
and r.sevrfromdate=t.sevrfromdate
Результат рекурсивного запроса
Работает как шарм. Спасибо!
В соответствии с руководством по вопросам не публикуйте изображения кода, данных, сообщений об ошибках и т. д. — скопируйте или введите текст в вопрос. Пожалуйста, ограничьте использование изображений для диаграмм или демонстрации ошибок рендеринга, вещей, которые невозможно точно описать с помощью текста.