Query for customer quotes by date range

Question

I have a table (Oracle):

customer_quote
------
int id
int part_key (fk)
int condition_code (fk)
number unit_price
int qty_quoted
date entry_date

With a query that selects data from the past year:

SELECT cq.part_key 
    , cq.condition_code 
    , COUNT(*) AS cq_count 
    , SUM(cq.unit_price * cq.qty_quoted) AS cq_total 
    , SUM(cq.qty_quoted) cq_qty
FROM customer_quote cq 
WHERE cq.entry_date > TO_DATE('09-Jun-2017', 'dd-mm-yyyy')
GROUP BY cq.part_key 
    , cq.condition_code 

But I needed the data to include parts with activity from the past two years but data older than 1 year shouldn't calculate into the totals. So I wrote this query:

SELECT cq.part_key 
    , cq.condition_code 
    , COUNT(case when cq.entry_date > TO_DATE('09-Jun-2017', 'dd-mm-yyyy') then 1 END) AS cq_count 
    , SUM(case when cq.entry_date > TO_DATE('09-Jun-2017', 'dd-mm-yyyy') then (cq.unit_price * cq.qty_quoted) END) AS cq_total 
    , SUM(case when cq.entry_date > TO_DATE('09-Jun-2017', 'dd-mm-yyyy') then cq.qty_quoted END) cq_qty
FROM customer_quote cq 
WHERE cq.entry_date > TO_DATE('09-Jun-2016', 'dd-mm-yyyy')
GROUP BY cq.part_key 
    , cq.condition_code 

The where date is 2 years ago but the case when dates are all 1 year ago. This query works, but is it optimal? I think there's probably a way to merge the case when statements into 1.

Performance is my main concern, readability is only marginally important.

Worth noting: the final SQL query will be a parameterized SQL Java object so the date will be a CASE WHEN cq.entry_date > ? THEN and inserted using PreparedStatement::setDate() so with my solution I will need to do setDate() x 4.

Answer 1

I think the query is optimal enough. The truth is query can retrieve a result only by accessing 2 years data, so given WHERE condition is must. And the query access rows only once. At performance aspect, table access method (through index or by full scan) is critical, but Oracle Optimizer will choose it by referring statistics information.

Answer 2

To optimize this query, I would only perform the date checks once, regardless of how Oracle optimizes internally.

, COUNT(case when cq.entry_date > TO_DATE('09-Jun-2017', 'dd-mm-yyyy') then 1 END) AS cq_count 
    , SUM(case when cq.entry_date > TO_DATE('09-Jun-2017', 'dd-mm-yyyy') then (cq.unit_price * cq.qty_quoted) END) AS cq_total 
    , SUM(case when cq.entry_date > TO_DATE('09-Jun-2017', 'dd-mm-yyyy') then cq.qty_quoted END) cq_qty
FROM customer_quote cq

could be rewritten as: Fiddle Oracle 11g Release2

select part_key 
    ,  condition_code 
    ,  count(recent) cq_count 
    ,  sum(case when recent = 1 then (unit_price * qty_quoted) end) cq_total 
    ,  sum(case when recent = 1 then qty_quoted end) cq_qty
from (
    select q.* 
        , case when entry_date > add_months(trunc(sysdate),-12) then 1 end recent
        from customer_quote q
    where entry_date > add_months(trunc(sysdate),-24)
) lookup 
group by part_key, condition_code
--order by 1, 2