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 >=1 then 1 else 0 END as "w/ Receipt",
CASE when pda.quantity_billed >=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 > to_date(:myDate,'DD-MON-YY')
)