Поиск по дате по каждой строке

Question

Есть 2 таблицы

--Результат подзапроса (отфильтрованных клиентов)
create table fq (idclient, idblank, datenter) as
    select 1, 1, date'2021-06-03'+0.5 from dual union all  
    select 5, 4, date'2021-03-13'+0.5 from dual union all 
    select 2, 1, date'2021-04-03'+0.5 from dual

--Вся таблица
create table t (idclient, idblank, datenter) as
    select 5, 13, date'2021-01-14'+0.5 from dual union all
    select 1, 1, date'2021-06-03'+0.5 from dual union all  
    select 1, 5, date'2021-02-13'+0.5 from dual union all 
    select 2, 14, date'2021-06-03'+0.5 from dual union all  
    select 3, 12, date'2021-02-17'+0.5 from dual union all 
    select 4, 9, date'2021-04-03'+0.5 from dual union all  
    select 5, 6, date'2021-01-12'+0.5 from dual union all 
    select 6, 7, date'2021-04-03'+0.5 from dual union all  
    select 5, 4, date'2021-03-13'+0.5 from dual union all 
    select 3, 3, date'2021-04-03'+0.5 from dual union all  
    select 4, 2, date'2021-02-13'+0.5 from dual union all 
    select 2, 1, date'2021-04-03'+0.5 from dual union all
    select 1, 10, date'2021-05-25'+0.5 from dual

Суть задачи - Узнать, заводилась ранее анкета по клиенту за 3 месяца назад.
Важно - Смотреть 3 месяца назад от даты заведения анкеты (индивидуально для каждого клиента).
Так же таблица fq - это результат подзапроса, по этому при поиске в таблице t нельзя учитывать дублирующиеся анкеты.

Я пытался решить следующей логикой :

Нужно найти последнюю дату заведённой анкеты по клиенту из запроса fq
Присоединить таблицу t для поиска по ней
От последней даты по клиенту, нужно глянуть может были другие анкеты за 3 месяца.

select t2.idclient, case when t1.idblank is not null then 1 end as priz_m3
,t1.datenter
from (select 
 idclient, idblank, datenter
from t ) t1
left join (select
  t.idclient, max(t.datenter) as datenter
 from t  join fq on t.idclient=fq.idclient
 group by t.idclient
) t2 on t1.idclient=t2.idclient 
  and t2.datenter between add_months(t1.datenter,-3) and t1.datenter-1

Ожидаемый результат

IDCLIENT   PRIZ_M3   DATENTER
1             1      25-MAY-21
2            NULL      NULL
5             1      14-JAN-21
5             1      12-JAN-21

По факту db<>fiddle

Answer 1

SELECT t1.idclient
      ,CASE
         WHEN t2.idclient IS NOT NULL THEN
          1
         ELSE
          NULL
       END AS is_enter_earlier -- 1 если заводилась ранее
      ,t2.datenter
  FROM fq t1
  LEFT JOIN t t2
    ON t1.idclient = t2.idclient
   AND t1.datenter != t2.datenter
   AND t2.datenter BETWEEN ADD_MONTHS(t1.datenter, -3) AND t1.datenter
 ORDER BY t1.idclient;

Answer 2

Сделал бы так:

select 
    fq.*, case 
          when add_months (fq.datenter, -3) <= prevFormIssued 
          then 'y' else 'n' end existLessAs3MonthOld
from fq
cross apply (
    select max (datenter) keep (dense_rank first order by datenter desc) prevFormIssued
    from t 
    where t.idclient = fq.idclient 
    and t.datenter < fq.datenter)

Результат (на db<>fiddle):

  IDCLIENT    IDBLANK DATENTER            E
---------- ---------- ------------------- -
         1          1 2021-06-03 12:00:00 y
         5          4 2021-03-13 12:00:00 y
         2          1 2021-04-03 12:00:00 n

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

Answer 3

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

select 
 t1.idclient
 ,t2.idblank
 ,case when t2.idclient is not null then 1 end as priz_m3
 ,t2.datenter
from ( 
  select distinct
  fq.idclient
  ,fq.idblank
  -- находим последний договор по клиенту
  ,max(t1.datenter) over (partition by t1.idclient) as datenter
 from fq
 left join t t1 on t1.idclient=fq.idclient
 )t1
left join t t2
 on t2.idclient=t1.idclient
/* После поиска макс даты, по какой то причине,
сравнение по полю datenter - перестало быть корректным, тут idblank и пригодился*/
 and t1.idblank != t2.idblank
 and t2.datenter between add_months(t1.datenter,-3) and t1.datenter

БЛОГ НА HUSL

Поиск по дате по каждой строке

Ответы (3 шт):