Q52797535, Oracle - rextester

Language: Layout:

drop table my_table555\\
create table my_table555(Case_ID int, Start_Date  date, End_Date date, STATUS varchar2(15))\\

insert into my_table555 values(123     ,date'2018-10-01',  date'2018-10-03'  ,'Close')\\
insert into my_table555 values(124     ,date'2018-10-02',  null  ,'Open')\\
insert into my_table555 values(125     ,date'2018-10-03',  date'2018-10-05'  ,'Close')\\
insert into my_table555 values(126     ,date'2018-10-04',  null  ,'Open')\\
insert into my_table555 values(127     ,date'2018-10-05',  date'2018-10-07'  ,'Close')\\
insert into my_table555 values(128     ,date'2018-10-06',  null  ,'Open')\\
insert into my_table555 values(129     ,date'2018-10-07',  date'2018-10-09'  ,'Close')\\
insert into my_table555 values(130     ,date'2018-10-08',  date'2018-10-10'  ,'Close')\\
insert into my_table555 values(131     ,date'2018-10-09',  null  ,'Open')\\

select week_no, sum(nvl(Inflow,0)) as Inflow, 
                sum(nvl(Outflow,0)) as Outflow, 
                sum(nvl(Total_Backlog,0)) as Total_Backlog
  from               
   (
    select to_char(Start_Date,'IW') Week_No, 
           count(CASE_ID) as Inflow,
           ( case when STATUS != 'Close' then count(CASE_ID) end ) as Total_Backlog,
           null Outflow 
      from my_table555
     group by to_char(Start_Date,'IW'), status
     union all
    select to_char(End_Date,'IW') Week_No, 
           null as Inflow, null as Total_Backlog,
           ( case when STATUS = 'Close' then count(CASE_ID) end ) as Outflow 
      from my_table555
     where End_Date is not null 
     group by to_char(End_Date,'IW'), status
    ) 
 group by week_no
 order by week_no


absolute service time: 1,41 sec