Date Range Report - Aggregation

Question

I have a few issues doing a typical report style SQL that I’m hoping someone with more experience might be able to help with.

I have the following tables

products

• product_id
• product_name
• product_category

product_defects

• product_id
• defect_date
• high_priority
• med_priority
• low_priority

calendar

• date

And what I want is to be able to generate a report that outlines the number of high / medium / low defects associated with each product category on each day e.g – even though data may not exist in product_defect for a particular day, in which case it should be returned as 0. Example:

product_category | date | high | medium | low

1 2012-10-01 1 5 6

2 2012-10-01 2 4 3

3 2012-10-01 1 5 6

1 2012-10-02 0 0 0

2 2012-10-02 2 4 3

3 2012-10-02 1 5 6

…

What I’ve done so far is:

• Create a lookup table called calendar which has a series of days in it going back/forward several years
• Right joined the lookup/product_defects table to get a series of dates so missing days can be marked as 0
• Used COALESCE and SUM to calculate totals and change any missing data to 0
• Used MIN / MAX on the defect_date to get the exact report range

I've banged my head on this for a few days now, hoping someone can help.

Thank you

Answer 1

You need to start with all combinations of products and dates, and then join in the defects:

select p.product_category, c.date, 
       coalesce(SUM(high_priority), 0) as high_priority,
       coalesce(SUM(med_priority), 0) as med_priority,
       coalesce(SUM(low_priority), 0) as low_priority
from product p cross join
     calendar c left outer join
     product_defects pd
     on pd.product_id = p.product_id and
        pd.date = c.date
group by p.product_category, c.date
order by 2, 1

(Note: this is untested, so may have syntax errors.)

Answer 2

something like this. I used dual instead of calendar which you can use or substitute calendar table.

eg with start date as 01-jan-2013 through to 15th jan 2013.

with dates as (select to_date('01/01/2013', 'dd/mm/yyyy') + rownum - 1 dte
                 from dual 
               connect by level <= to_date('15/01/2013', 'dd/mm/yyyy') 
                         - to_date('01/01/2013', 'dd/mm/yyyy') + 1)
select dt.dte, p.product_id, p.product_name, 
       sum(d.high_priority), sum(d.med_priority), sum(d.low_priority)
  from products p
       inner join product_defects d
               on d.product_id = p.product_id
       right outer join dates dt
                     on dt.dte = d.defect_date -- trunc(d.defect_date) if you store with a time element.
 group by dt.dte, p.product_id, p.product_name
 order by dt.dte;

Answer 3

So this uses a sub-factory querying clause (cte) to aggregate all the defects for each category for each day. I used this construct to make the logic clearer; there are other ways to do it. The sub-query is then outer-joined to the calendar table.

with cte as 
    ( select p.product_category
             , d.defect_date
             , sum(pd.high_priority) as high_priority
             , sum(pd.med_priority) as med_priority
             , sum(pd.low_priority) as low_priority
      from product p
           join product_defect pd
                on (pd.product_id = p.product_id )
     group by p.product_category
             , d.defect_date )
select cte.product_category
       , cal.date 
       , nvl(cte.high_priority, 0) as high_priority
       , nvl(cte.med_priority, 0) as med_priority
       , nvl(cte.low_priority, 0) as low_priority
from calendar cal
     left outer join cte 
        on cal.date = cte.defect_date
order by cte.product_category
       , cal.date 

Answer 4

Calendar table example only. Increase the number of months back from -24 (2 years) to any number - copy/paste the code:

-- 2 years back by date  --
SELECT TRUNC(SYSDATE, 'YEAR') - LEVEL AS mydate
 FROM dual
CONNECT BY LEVEL <= TRUNC(SYSDATE, 'yy') - TRUNC(Add_Months(SYSDATE, -24), 'yy')
/

Add more dates:

-- 2 years back by date and week  --
SELECT mydate
 , TRUNC(mydate, 'iw') wk_starts
 , TRUNC(mydate, 'iw') + 7 - 1/86400 wk_ends
 , TO_NUMBER (TO_CHAR (mydate, 'IW')) ISO_wk#  
FROM
(
SELECT TRUNC(SYSDATE, 'YEAR') - LEVEL AS mydate
  FROM dual
CONNECT BY LEVEL <= TRUNC(SYSDATE, 'yy') - TRUNC(Add_Months(SYSDATE, -24), 'yy')
)
/

Post create table and inserts scripts to answer the rest of your questions or use sqlfiddle...