CODEWITHSUNDEEP
sqloracle-databasegroup-byaggregation
Praveen Verma
Praveen Verma

Reputation: 41

Oracle SQL query to get Weekly Records

I have oracle database table 'my_table' with few sample rows as following-

Case_ID Start_Date  End_Date    STATUS
123     01/10/2018  03/10/2018  Close
124     02/10/2018              Open
125     03/10/2018  05/10/2018  Close
126     04/10/2018              Open
127     05/10/2018  07/10/2018  Close
128     06/10/2018              Open
129     07/10/2018  09/10/2018  Close
130     08/10/2018  10/10/2018  Close
131     09/10/2018              Open

I want to get output in following format -

Week_No  Inflow Outflow  Total_Backlog
40       7       4       3
41       2       1       4

How to combine following three queries to get the desired output in above format using one query?

 SELECT to_char(Start_Date,'IW') Week_No, count(CASE_ID) as Inflow 
   FROM my_table;

 SELECT to_char(End_Date,'IW') Week_No, count(CASE_ID) as Outflow  
   FROM my_table 
  WHERE status='Close';

 SELECT to_char(Start_Date,'IW') Week_No, count(CASE_ID) as Total_Backlog  
   FROM my_table 
  WHERE status <> 'Close';

Upvotes: 1

Views: 128

Answers (1)

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65228

You might use:

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_table
     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_table
     where End_Date is not null
     group by to_char(End_Date,'IW'), status
    ) 
 group by week_no
 order by week_no;

 WEEK_NO    INFLOW  OUTFLOW TOTAL_BACKLOG
    40         7       3          3
    41         2       2          1

Rextester Demo

or you may prefer in a little different way as :

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,
           count( case when STATUS != 'Close' then CASE_ID end ) as Total_Backlog,
           null Outflow 
      from my_table
     group by to_char(Start_Date,'IW')
     union all
    select to_char(End_Date,'IW') Week_No, 
           null as Inflow, null as Total_Backlog,
           count( case when STATUS = 'Close' then CASE_ID end ) as Outflow 
      from my_table
     where End_Date is not null 
     group by to_char(End_Date,'IW')
    ) 
 group by week_no
 order by week_no;

 WEEK_NO    INFLOW  OUTFLOW TOTAL_BACKLOG
    40         7       3          3
    41         2       2          1

Upvotes: 1

Related Questions