Oracle - Get Months/Weeks/Days for a Date range, then outer join statistics so that there are no missing Months/Weeks/Days.
 
I had do produce data so that graphs could be build
  • For a date range
  • By Months/Weeks/Days
  • Where sometimes there are zeroes (gaps) in the data. for whichever Months/Weeks/Days
Lets say that you need to graph
  • 4 months of monthly summarized Coupon Data
  • in date range ‘1/18/2007’ to ‘4/12/2007’
  • for 3 Coupons (A, B, C)
  • from CouponStats in another table that points to Coupons.
 
In order to get zeros,
And to pragmatically accumulate the graph script,
I naively wrote a lot of code that
  • figured out each month intervals
  • and went to the database 12 times (4 months times 3 coupons)
  • a major performance blunder
  • you can imagine what would happen for a ten year graph for 50 coupons. <blush>
 One I figured out that I needed to "laydown" the Months/Weeks/Days data first, then outer join the Coupons & the stats, it became rather easy, via the Oracle With clause.
Then I googled and found this wondderful posting
 
Kevin’s posting taught me how to solve my problem via the Oracle ‘with’ clause,
and he gave a good explanation of the Oracle stuff, which I wil;l not repeat :)
 
Here is one piece of SQL that returns everything I need J
 
with
   months as (
        select add_months (trunc (to_date(‘1/18/2007’,'MM/DD/YYYY'), 'MM'), 1*Level -1)
        Month   FROM Dual
        CONNECT BY Level <= MONTHS_BETWEEN(to_date(‘4/12/2007’,'MM/DD/YYYY'), to_date(‘1/18/2007’,'MM/DD/YYYY')) + 1
        order by month
        )
   ,coups as (
        select
         c.Coupon_Id as Coupon_Id
         ,c.Coupon_Name as Coupon_Name
         from Coupons c
         where c.Coupon_Id in (
             select coupon_id from coupon_brand_campaign
             where coupon_Id in (A, B, C))
         )
    ,Months_coups as (
        select * from months, coups
        order by month)
    ,coupStats as (
        select
         cs.Coupon_Id as Coupon_Id
         , trunc(cs.SOURCE_DATE,'MM') as month
         , nvl(sum(cs.Prints), 0) as PrintsSum
         , nvl(sum(cs.Prints_Goal), 0) as Prints_Goal
         , nvl(sum(cs.Redemptions), 0) as RedemptionsSum
         , nvl(sum(cs.Redemptions_Goal), 0) as Redemptions_Goal
         from Coupon_Stats cs
         where cs.SOURCE_DATE between to_date(‘1/18/2007’,'MM/DD/YYYY') and to_date(‘4/12/2007’,'MM/DD/YYYY')
         and cs.Coupon_Id in (
             select coupon_id from coupon_brand_campaign
             where coupon_Id in (A, B, C))
         group by cs.Coupon_Id, trunc(cs.SOURCE_DATE,'MM')
         )
select mc.month as StartOfMonth, last_day(mc.month) as EndOfMonth, mc.Coupon_Id, mc.Coupon_Name
         , nvl(cs.PrintsSum, 0) as PrintsSum
         , nvl(cs.Prints_Goal, 0) as Prints_Goal
         , nvl(cs.RedemptionsSum, 0) as RedemptionsSum
         , nvl(cs.Redemptions_Goal, 0) as Redemptions_Goal
from Months_coups mc
left outer join coupStats cs
on cs.month = mc.month
and cs.Coupon_Id = mc.Coupon_Id
order by mc.Coupon_Id, mc.month