A table of offers for different clients:

PERIOD |CELLPHONE   | IDENTIFICATION | FIRST_DATE   | LAST_DATE  | UPSELLING | IPHONE 
202208  56961424344     152783337       09/08/2022    23/08/2022       1         0
202208  56961424344     152783337       09/08/2022    23/08/2022       0         1
202208  56961424344     152783337       24/08/2022    27/09/2022       1         0

A table of attentions on different channels:

PERIOD |   DATE    | IDENTIFICATION | CELLPHONE  | CALL_CENTER | DIGITAL | PUBLIC
202208   09/08/2022   NULL           56961424344        1          0         0
202208   11/08/2022   152783337      56961424344        1          0         0
202208   26/08/2022   152783337      56961424344        0          1         0

What I want:

PERIOD | FIRST_DATE | LAST_DATE | CELLPHONE | IDENTIFICATION | UPSELLING | IPHONE | CALL_CENTER | DIGITAL|..
202208   09/08/2022   23/08/2022 56961424344    152783337         1          1           1          0
         24/08/2022   27/09/2022 56961424344    152783337         1          0           0          1

But I get duplicate rows, because of the many to many relationship.

The idea is to group by the dates in which the offer will be valid and see if the different channels received a call.

If the channel received more than 1 call from that client in that time interval, I just want a flag 1.

WITH attentions_since_to AS
( 
 SELECT DISTINCT period,
                 identification,
                 cellphone,
                 call_center,
                 public,
                 digital,
                 first_date,
                 last_date
    FROM (SELECT a.*, o.first_date, o.last_date
            FROM attentions a
            LEFT JOIN offers o
              ON a.cellphone = o.cellphone
             AND a.date BETWEEN o.first_date AND o.last_date)
)

CodePudding user response：

We can start by aggregating the offers table so we get just get one row per "offer"; then, we can use a lateral join to retrieve and aggregate the attentions that correspond to the identification and date range of each offer.

select a.*, o.*  
from (
    select period, first_date, last_date, cellphone, identification,
        max(upselling) upselling, max(iphone) iphone,
    from offers
    group by period, first_date, last_date, cellphone, identification
) o
outer apply (
    select max(a.call_center) call_center, max(digital) digital, max(public) public
    from attentions a
    where a.period = o.period 
      and a.identification = o.identification 
      and a.date between o.first_date and o.last_date
) a

There are a few assumptions here:

• an offer is a tuple of (period, cellphone, identification, first_date, last_date)
• tables offers and attentions connect through columns period and identification (along with the date range of course)

Side note : lateral join are supported in Oracle since version 12c.