Geeks With Blogs

News

Rodney Vinyard - .NET & SQL Developer When all is said and done, more will be said than done
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

Related Posts on Geeks With Blogs Matching Categories

Comments on this post: Oracle - Get Months/Weeks/Days for a Date range, then outer join statistics so that there are no missing Months/Weeks/Days.