CODEWITHSUNDEEP
sqloracle-databaseoracle11gmin
zundarz
zundarz

Reputation: 1594

Reporting MIN(VAL) for Each Partitioned Value in Another Column

Oracle 11g

How can I return single row of min(email_sent) for each email_type per ID?

That is, I'd like to get the first invitation date and the first confirmation date into a single row.

with mailings  as (
select 1 as recipient_id, 'INVITE' as email_type, to_date('JAN-01-2020','MON-DD-YYYY') as email_sent from dual union all
select 1 as recipient_id, 'INVITE' as email_type, to_date('JAN-02-2020','MON-DD-YYYY') as email_sent from dual union all
select 1 as recipient_id, 'INVITE' as email_type, to_date('JAN-03-2020','MON-DD-YYYY') as email_sent from dual union all
select 1 as recipient_id, 'CONFIRM'as email_type, to_date('JAN-10-2020','MON-DD-YYYY') as email_sent from dual union all
select 1 as recipient_id, 'CONFIRM'as email_type, to_date('JAN-11-2020','MON-DD-YYYY') as email_sent from dual
)
 select *
from (
select recipient_id, 
       email_type, 
       min(email_sent) over (partition by recipient_id, email_type) as first_invite,
       min(email_sent) over (partition by recipient_id, email_type) as first_confirmation,
       rank()          over (partition by recipient_id, email_type order by email_sent) as email_type
                                                        from mailings
                                                    
)
where email_type=1;

Desired Result: Report the dates for the first invitation and the first confirmation.

Recipient_ID FIRST_INVITE FIRST_CONFIRMATION

1 JAN-01-2020 JAN-10-2020

Upvotes: 0

Views: 31

Answers (1)

gsalem
gsalem

Reputation: 2028

Here you go, as per your wording, not your result (as you first sent, but you show last sent):

with mailings  as (
select 1 as recipient_id, 'INVITE' as email_type, to_date('JAN-01-2020','MON-DD-YYYY') as email_sent from dual union all
select 1 , 'INVITE' , to_date('JAN-02-2020','MON-DD-YYYY')  from dual union all
select 1 , 'INVITE' , to_date('JAN-03-2020','MON-DD-YYYY')  from dual union all
select 1 , 'CONFIRM', to_date('JAN-10-2020','MON-DD-YYYY')  from dual union all
select 1 , 'CONFIRM', to_date('JAN-11-2020','MON-DD-YYYY')  from dual
)
select recipient_id, 
       min(case when email_type='INVITE' then email_sent else null end) sent, 
       min(case when email_type='CONFIRM' then email_sent else null end) confirmed 
from mailings
group by recipient_id;

RECIPIENT_ID    SENT        CONFIRMED
1               01-JAN-20   10-JAN-20

Upvotes: 1

Related Questions