SQL to find the number of days taken to deliver a product

 

 Expected Output:

 

  

create table t_order(prod varchar2(10),

status_date date,

customer varchar2(30),

status varchar2(10));

insert into t_order values('P1',to_date('01/JUL/2020','DD/MON/YYYY'),'RAVI','ordered');

insert into t_order values('P1',to_date('03/JUL/2020','DD/MON/YYYY'),'RAVI','dispatched');

insert into t_order values('P1',to_date('05/JUL/2020','DD/MON/YYYY'),'RAVI','delivered');

 

insert into t_order values('P2',to_date('01/JUL/2020','DD/MON/YYYY'),'SRIKANTH','ordered');

insert into t_order values('P2',to_date('05/JUL/2020','DD/MON/YYYY'),'SRIKANTH','dispatched');

insert into t_order values('P2',to_date('10/JUL/2020','DD/MON/YYYY'),'SRIKANTH','delivered');

insert into t_order values('P1',to_date('01/JUL/2020','DD/MON/YYYY'),'SURESH KODI','ordered');

insert into t_order values('P1',to_date('05/JUL/2020','DD/MON/YYYY'),'SURESH KODI','dispatched');

COMMIT;

 

select prod,customer,status_date,status from t_order

where STATUS in('ordered','delivered');

 

select prod,customer,status_date,status,

case when  status = 'ordered' then status_date end o_date,

case when  status = 'delivered' then status_date end o_date

from t_order

where STATUS in('ordered','delivered');

 

select PROD,CUSTOMER,

max(case when  status = 'ordered' then status_date end) o_date,

max(case when  status = 'delivered' then status_date end) o_date

from t_order

where STATUS in('ordered','delivered')

group by PROD,CUSTOMER ;

 

select PROD,CUSTOMER,

max(case when  status = 'delivered' then status_date end)-max(case when  status = 'ordered' then status_date end) o_date

from t_order

where STATUS in('ordered','delivered')

group by PROD,CUSTOMER ;