sql – Date search for each line

Question:

There are 2 tables

--Результат подзапроса (отфильтрованных клиентов)
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

Суть задачи – Find out if a customer's profile was started earlier 3 months ago.
Важно – Look 3 months ago from the date of the questionnaire creation (individually for each client).
Also, table fq is the result of a subquery, therefore, when searching in table t duplicate questionnaires cannot be taken into account.

I tried to solve with the following logic:

  1. You need to find the last date of the established customer profile from the fq request
  2. Join table t to search for it
  3. From the last date on the client, you need to look maybe there were other profiles for 3 months.
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

Expected Result

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

In fact db <> fiddle

Answer:

Would do it like this:

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)

Result (on 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

The expected result in the question is most likely different from the task description.

Post Views: 0
Scroll to Top