In a previous post I had discussed how to densify a sales report by filling in missing date rows. I accomplished that by referencing an existing table of calendar dates to which I outer joined my sales data. For those who don’t have a handy dates table (as I do in our E-Business Suite), I provided a script to create such a table.

But probably many of you may not be able to (or are allowed to) create a database object just for a report. But, if you noodle around with Oracle long enough you’ll eventually learn nifty alternative ways of doing just about everything.

Using Oracle’s CONNECT BY LEVEL clause we can generate as many rows as we want in pure SQL. And these rows can be interpreted as dates, or months, or any interval we wish. Try this little row generating statement:

select level
from dual
connect by level <= 10;

Although DUAL only has one row, we can generate as many rows as we need, ten in this case. Let’s change these rows of numbers to rows of dates:

elect to_date('01-JAN-09','DD-MON-RR') + level - 1 cal_date
from dual
connect by level between to_date('31-JAN-09','DD-MON-RR') - to_date('01-JAN-09','DD-MON-RR') + 1;

Wrap this in an inline view and you have your virtual dates table! You could also make each row be month or quarter or year increments with some judicious use of temporal functions in Oracle.

If you are using bind variables to specify your date range in your query, you can then simply use the begin date and end date bind variable in your row generating query as well as in your main data query.

select to_date('01-JAN-09','DD-MON-RR') + level - 1 cal_date
from dual
connect by level <= to_date(:begin_date,'DD-MON-RR') - to_date(:end_date,'DD-MON-RR') + 1;

However, just for fun let’s just reference the date range in our row generating query by creating a named sub-query using Oracle’s WITH AS syntax:

with dates as (select to_date('01-JAN-09','DD-MON-RR') beg_date, to_date('31-JAN-09','DD-MON-RR') end_date from dual)
select dates.beg_date + level - 1 cal_date
from dual
cross join dates
connect by level <= dates.end_date - dates.beg_date + 1;

The CROSS JOIN creates a Cartesian product, but the named sub-query and DUAL only contain one row each, so this is not a problem. Almost there, now let’s just add in the original sales query and move our row generator into an inline view:

with dates as (select to_date('01-JAN-09','DD-MON-RR') beg_date, to_date('31-JAN-09','DD-MON-RR') end_date from dual)
select d.cal_date, nvl(s.total_sales,0) total_sales
from (
      select dates.beg_date + level - 1 cal_date
      from dual
      cross join dates
      connect by level <= dates.end_date - dates.beg_date + 1
      ) d
left outer join (
                  SELECT TRUNC (sales_date) sales_date,
                          SUM (quantity * price) total_sales
                     FROM sparse_sales
                    WHERE sales_date BETWEEN dates.beg_date AND dates.end_date +.99999
                  GROUP BY TRUNC (sales_date)
                ) s on s.sales_date = d.cal_date
order by 1;

In most cases if you can rely on pure SQL you’re much better off. I like this row generating technique better than relying on a physical table of dates, as it’s more portable.

What Do You Think?