Generating business summary stats in SQL dynamically, based on an inputted date I have the following Oracle SQL query to generate some system-stats. I am interested in making it a more efficient and readable query.

SELECT  
    SUM("Req Lines") as "Requisitions Created", 
    SUM("Approved Req") AS "Requisitions Approved", 
    SUM("Non Approved PO") AS "PO Started", 
    SUM("Approved PO") AS "PO Approved", 
    SUM("w/ Receipt") AS "Receipts Completed", 
    SUM("w/ Invoice") AS "Invoices Completed" 
FROM( 
  SELECT DISTINCT prha.Segment1 AS "Req#",   

    pha.Segment1 as "PO#", 
    CASE when prha.creation_date between to_date(:myDate,'DD-MON-YY') and to_date(:myDate,'DD-MON-YY') + 7  then 'Week 1'  
        when prha.creation_date between to_date(:myDate,'DD-MON-YY') + 7 and to_date(:myDate,'DD-MON-YY') + 14  then 'Week 2'  
        when prha.creation_date between to_date(:myDate,'DD-MON-YY') + 14 and to_date(:myDate,'DD-MON-YY') + 21 then 'Week 3' 
        when prha.creation_date between to_date(:myDate,'DD-MON-YY') + 21 and to_date(:myDate,'DD-MON-YY') + 28  then 'Week 4'  
      else 'After Month 1' END as "Req_Created",     1  as "Req Lines", 
      prha.authorization_status as "Req Status", 

    CASE when prha.authorization_status='APPROVED' then 1 else 0 END as "Approved Req", 
    CASE when pha.authorization_status='APPROVED' then 1 else 0 END as "Approved PO", 
    pha.authorization_status, 
    CASE when (pha.authorization_status = 'APPROVED' or pha.authorization_status is null) then 0 else 1 END as "Non Approved PO", 
    CASE when pda.quantity_delivered &gt;=1 then 1 else 0 END as "w/ Receipt", 
    CASE when pda.quantity_billed &gt;=1 then 1 else 0 END as "w/ Invoice"


  FROM apps.po_requisition_lines_all prl,  
    apps.po_requisition_headers_all prha,  
    po.po_req_distributions_all prda,  
    po.po_distributions_all pda,  
    po.po_headers_all pha 
  where 1=1  
  and prl.requisition_header_id= prha.requisition_header_id 
  and prl.requisition_line_id= prda.requisition_line_id 
  and prda.distribution_id= pda.req_distribution_id(+) 
  and pha.po_header_id(+)=pda.po_header_id 


  AND prda.creation_date &gt; to_date(:myDate,'DD-MON-YY') 
)
It is possible to simplify this:

CASE when prha.creation_date between to_date(:myDate,'DD-MON-YY') and to_date(:myDate,'DD-MON-YY') + 7  then 'Week 1'  
    when prha.creation_date between to_date(:myDate,'DD-MON-YY') + 7 and to_date(:myDate,'DD-MON-YY') + 14  then 'Week 2'  
    when prha.creation_date between to_date(:myDate,'DD-MON-YY') + 14 and to_date(:myDate,'DD-MON-YY') + 21 then 'Week 3' 
    when prha.creation_date between to_date(:myDate,'DD-MON-YY') + 21 and to_date(:myDate,'DD-MON-YY') + 28  then 'Week 4'  
  else 'After Month 1' END as "Req_Created",     1  as "Req Lines", 


In Oracle SQL, you can extract a date from the other date. Result is a number, which equals to day count between dates (fractional part is hours, minutes and seconds). So, prha.creation_date - to_date(:myDate,'DD-MON-YY') gives number of days, ceil((prha.creation_date - to_date(:myDate,'DD-MON-YY' - 1) / 7) gives number of week. Here is the code:

case when prha.creation_date - to_date(:myDate,'DD-MON-YY') &gt; 28 
       then 'After Month 1'
     else 'Week ' || ceil((prha.creation_date - to_date(:myDate,'DD-MON-YY') - 1) / 7)
     end as "Req_Created"


The following expression:

CASE when prha.authorization_status='APPROVED' then 1 else 0 END as "Approved Req"


could be replaced with decode function:

decode(prha.authorization_status, 'APPROVED', 1, 0) as "Approved Req"


decode is more brief, but has its own limitations. For more details, see documentation.