November 20, 2006 at 7:27 am
I've written the following to help track unit/sales/inventory movement at our stores. I am calculating a pre period and a post period for 2005 and 2006 for each measure. Basically each of our stores were reset during a specific week and we are looking at the pre period (13 weeks) and the post period (week reset plus all post weeks). What I have works but it is long and will still require some manual editing each week. For example last week was our fiscal week 41, so I'll have to update that week each week. Can someone please suggest something easier? I started looking at doing something with declaring each week etc...but it goes a little beyond my skills at this point. If someone could at least get me on the right track I should be able to figure it out.
cast(s.hd_byo_nbr as varchar) + + '(' + cast(s.store_nbr as varchar) + ')'+ ' ' + + s.store_name [Store],
s.hd_byo_nbr [BYO],
store_nbr [Store#],
'(' + cast(i.hd_sub_class as varchar) + ')' +' ' + c.hd_sub_class_name [SubName],
i.hd_sku [SKU],
rapid_refresh_date [RRDate],
case
when rr_week = 24 then datediff(wk,'2006-07-10',d.maxdate)
when rr_week = 25 then datediff(wk,'2006-07-17',d.maxdate)
when rr_week = 26 then datediff(wk,'2006-07-24',d.maxdate)
when rr_week = 27 then datediff(wk,'2006-07-31',d.maxdate)
when rr_week = 28 then datediff(wk,'2006-08-07',d.maxdate)
when rr_week = 29 then datediff(wk,'2006-08-14',d.maxdate)
when rr_week = 30 then datediff(wk,'2006-08-21',d.maxdate)
when rr_week = 31 then datediff(wk,'2006-08-28',d.maxdate)
when rr_week = 32 then datediff(wk,'2006-09-04',d.maxdate)
when rr_week = 33 then datediff(wk,'2006-09-11',d.maxdate)
when rr_week = 34 then datediff(wk,'2006-09-18',d.maxdate)
when rr_week = 35 then datediff(wk,'2006-09-25',d.maxdate)
when rr_week = 36 then datediff(wk,'2006-10-02',d.maxdate)
when rr_week = 37 then datediff(wk,'2006-10-09',d.maxdate)
when rr_week = 38 then datediff(wk,'2006-10-16',d.maxdate)
when rr_week = 39 then datediff(wk,'2006-10-23',d.maxdate)
when rr_week = 40 then datediff(wk,'2006-10-30',d.maxdate)
when rr_week = 41 then datediff(wk,'2006-11-06',d.maxdate)
when rr_week = 42 then datediff(wk,'2006-11-13',d.maxdate)
when rr_week = 43 then datediff(wk,'2006-11-20',d.maxdate)
when rr_week = 44 then datediff(wk,'2006-11-27',d.maxdate)
when rr_week = 45 then datediff(wk,'2006-12-04',d.maxdate)
when rr_week = 46 then datediff(wk,'2006-12-11',d.maxdate)
when rr_week = 47 then datediff(wk,'2006-12-18',d.maxdate)
when rr_week = 48 then datediff(wk,'2006-12-26',d.maxdate)
else 0 end [PostWeekCount],
when rapid_refresh_date = '2006-07-10' and b.reporting_year = 2005 and
fiscal_wk in (11,12,13,14,15,16,17,18,19,20,21,22,23) then on_hand
when rapid_refresh_date = '2006-07-17' and b.reporting_year = 2005 and
fiscal_wk in (12,13,14,15,16,17,18,19,20,21,22,23,24) then on_hand
when rapid_refresh_date = '2006-07-24' and b.reporting_year = 2005 and
fiscal_wk in (13,14,15,16,17,18,19,20,21,22,23,24,25) then on_hand
when rapid_refresh_date = '2006-07-31' and b.reporting_year = 2005 and
fiscal_wk in (14,15,16,17,18,19,20,21,22,23,24,25,26) then on_hand
when rapid_refresh_date = '2006-08-07' and b.reporting_year = 2005 and
fiscal_wk in (15,16,17,18,19,20,21,22,23,24,25,26,27) then on_hand
when rapid_refresh_date = '2006-08-14' and b.reporting_year = 2005 and
fiscal_wk in (16,17,18,19,20,21,22,23,24,25,26,27,28) then on_hand
when rapid_refresh_date = '2006-08-21' and b.reporting_year = 2005 and
fiscal_wk in (17,18,19,20,21,22,23,24,25,26,27,28,29) then on_hand
when rapid_refresh_date = '2006-08-28' and b.reporting_year = 2005 and
fiscal_wk in (18,19,20,21,22,23,24,25,26,27,28,29,30) then on_hand
when rapid_refresh_date = '2006-09-04' and b.reporting_year = 2005 and
fiscal_wk in (19,20,21,22,23,24,25,26,27,28,29,30,31) then on_hand
when rapid_refresh_date = '2006-09-11' and b.reporting_year = 2005 and
fiscal_wk in (20,21,22,23,24,25,26,27,28,29,30,31,32) then on_hand
when rapid_refresh_date = '2006-09-18' and b.reporting_year = 2005 and
fiscal_wk in (21,22,23,24,25,26,27,28,29,30,31,32,33) then on_hand
when rapid_refresh_date = '2006-09-25' and b.reporting_year = 2005 and
fiscal_wk in (22,23,24,25,26,27,28,29,30,31,32,33,34)then on_hand
when rapid_refresh_date = '2006-10-02' and b.reporting_year = 2005 and
fiscal_wk in (23,24,25,26,27,28,29,30,31,32,33,34,35)then on_hand
when rapid_refresh_date = '2006-10-09' and b.reporting_year = 2005 and
fiscal_wk in (24,25,26,27,28,29,30,31,32,33,34,35,36)then on_hand
when rapid_refresh_date = '2006-10-16' and b.reporting_year = 2005 and
fiscal_wk in (25,26,27,28,29,30,31,32,33,34,35,36,37)then on_hand
when rapid_refresh_date = '2006-10-23' and b.reporting_year = 2005 and
fiscal_wk in (26,27,28,29,30,31,32,33,34,35,36,37,38)then on_hand
when rapid_refresh_date = '2006-10-30' and b.reporting_year = 2005 and
fiscal_wk in (27,28,29,30,31,32,33,34,35,36,37,38,39)then on_hand
when rapid_refresh_date = '2006-11-06' and b.reporting_year = 2005 and
fiscal_wk in (28,29,30,31,32,33,34,35,36,37,38,39,40)then on_hand
when rapid_refresh_date = '2006-11-13' and b.reporting_year = 2005 and
fiscal_wk in (29,30,31,32,33,34,35,36,37,38,39,40,41)then on_hand
else 0 end) [2005PreOnHand],
when rapid_refresh_date = '2006-07-10' and b.reporting_year = 2006 and
fiscal_wk in (11,12,13,14,15,16,17,18,19,20,21,22,23) then on_hand
when rapid_refresh_date = '2006-07-17' and b.reporting_year = 2006 and
fiscal_wk in (12,13,14,15,16,17,18,19,20,21,22,23,24) then on_hand
when rapid_refresh_date = '2006-07-24' and b.reporting_year = 2006 and
fiscal_wk in (13,14,15,16,17,18,19,20,21,22,23,24,25) then on_hand
when rapid_refresh_date = '2006-07-31' and b.reporting_year = 2006 and
fiscal_wk in (14,15,16,17,18,19,20,21,22,23,24,25,26) then on_hand
when rapid_refresh_date = '2006-08-07' and b.reporting_year = 2006 and
fiscal_wk in (15,16,17,18,19,20,21,22,23,24,25,26,27) then on_hand
when rapid_refresh_date = '2006-08-14' and b.reporting_year = 2006 and
fiscal_wk in (16,17,18,19,20,21,22,23,24,25,26,27,28) then on_hand
when rapid_refresh_date = '2006-08-21' and b.reporting_year = 2006 and
fiscal_wk in (17,18,19,20,21,22,23,24,25,26,27,28,29) then on_hand
when rapid_refresh_date = '2006-08-28' and b.reporting_year = 2006 and
fiscal_wk in (18,19,20,21,22,23,24,25,26,27,28,29,30) then on_hand
when rapid_refresh_date = '2006-09-04' and b.reporting_year = 2006 and
fiscal_wk in (19,20,21,22,23,24,25,26,27,28,29,30,31) then on_hand
when rapid_refresh_date = '2006-09-11' and b.reporting_year = 2006 and
fiscal_wk in (20,21,22,23,24,25,26,27,28,29,30,31,32) then on_hand
when rapid_refresh_date = '2006-09-18' and b.reporting_year = 2006 and
fiscal_wk in (21,22,23,24,25,26,27,28,29,30,31,32,33) then on_hand
when rapid_refresh_date = '2006-09-25' and b.reporting_year = 2006 and
fiscal_wk in (22,23,24,25,26,27,28,29,30,31,32,33,34)then on_hand
when rapid_refresh_date = '2006-10-02' and b.reporting_year = 2006 and
fiscal_wk in (23,24,25,26,27,28,29,30,31,32,33,34,35)then on_hand
when rapid_refresh_date = '2006-10-09' and b.reporting_year = 2006 and
fiscal_wk in (24,25,26,27,28,29,30,31,32,33,34,35,36)then on_hand
when rapid_refresh_date = '2006-10-16' and b.reporting_year = 2006 and
fiscal_wk in (25,26,27,28,29,30,31,32,33,34,35,36,37)then on_hand
when rapid_refresh_date = '2006-10-23' and b.reporting_year = 2006 and
fiscal_wk in (26,27,28,29,30,31,32,33,34,35,36,37,38)then on_hand
when rapid_refresh_date = '2006-10-30' and b.reporting_year = 2006 and
fiscal_wk in (27,28,29,30,31,32,33,34,35,36,37,38,39)then on_hand
when rapid_refresh_date = '2006-11-06' and b.reporting_year = 2006 and
fiscal_wk in (28,29,30,31,32,33,34,35,36,37,38,39,40)then on_hand
when rapid_refresh_date = '2006-11-13' and b.reporting_year = 2006 and
fiscal_wk in (29,30,31,32,33,34,35,36,37,38,39,40,41)then on_hand
else 0 end) [2006PreOnHand],
fiscal_wk between 24 and 41 then on_hand
when rapid_refresh_date = '2006-07-17' and b.reporting_year = 2005 and
fiscal_wk between 25 and 41 then on_hand
when rapid_refresh_date = '2006-07-24' and b.reporting_year = 2005 and
fiscal_wk between 26 and 41 then on_hand
when rapid_refresh_date = '2006-07-31' and b.reporting_year = 2005 and
fiscal_wk between 27 and 41 then on_hand
when rapid_refresh_date = '2006-08-07' and b.reporting_year = 2005 and
fiscal_wk between 28 and 41 then on_hand
when rapid_refresh_date = '2006-08-14' and b.reporting_year = 2005 and
fiscal_wk between 29 and 41 then on_hand
when rapid_refresh_date = '2006-08-21' and b.reporting_year = 2005 and
fiscal_wk between 30 and 41 then on_hand
when rapid_refresh_date = '2006-08-28' and b.reporting_year = 2005 and
fiscal_wk between 31 and 41 then on_hand
when rapid_refresh_date = '2006-09-04' and b.reporting_year = 2005 and
fiscal_wk between 32 and 41 then on_hand
when rapid_refresh_date = '2006-09-11' and b.reporting_year = 2005 and
fiscal_wk between 33 and 41 then on_hand
when rapid_refresh_date = '2006-09-18' and b.reporting_year = 2005 and
fiscal_wk between 34 and 41 then on_hand
when rapid_refresh_date = '2006-09-25' and b.reporting_year = 2005 and
fiscal_wk between 35 and 41 then on_hand
when rapid_refresh_date = '2006-10-02' and b.reporting_year = 2005 and
fiscal_wk between 36 and 41 then on_hand
when rapid_refresh_date = '2006-10-09' and b.reporting_year = 2005 and
fiscal_wk between 37 and 41 then on_hand
when rapid_refresh_date = '2006-10-16' and b.reporting_year = 2005 and
fiscal_wk between 38 and 41 then on_hand
when rapid_refresh_date = '2006-10-23' and b.reporting_year = 2005 and
fiscal_wk between 39 and 41 then on_hand
when rapid_refresh_date = '2006-10-30' and b.reporting_year = 2005 and
fiscal_wk between 40 and 41 then on_hand
when rapid_refresh_date = '2006-11-06' and b.reporting_year = 2005 and
fiscal_wk between 41 and 41 then on_hand
when rapid_refresh_date = '2006-11-13' and b.reporting_year = 2005 and
fiscal_wk between 42 and 41 then on_hand
else 0 end) [2005PostOnHand],
fiscal_wk between 24 and 41 then on_hand
when rapid_refresh_date = '2006-07-17' and b.reporting_year = 2006 and
fiscal_wk between 25 and 41 then on_hand
when rapid_refresh_date = '2006-07-24' and b.reporting_year = 2006 and
fiscal_wk between 26 and 41 then on_hand
when rapid_refresh_date = '2006-07-31' and b.reporting_year = 2006 and
fiscal_wk between 27 and 41 then on_hand
when rapid_refresh_date = '2006-08-07' and b.reporting_year = 2006 and
fiscal_wk between 28 and 41 then on_hand
when rapid_refresh_date = '2006-08-14' and b.reporting_year = 2006 and
fiscal_wk between 29 and 41 then on_hand
when rapid_refresh_date = '2006-08-21' and b.reporting_year = 2006 and
fiscal_wk between 30 and 41 then on_hand
when rapid_refresh_date = '2006-08-28' and b.reporting_year = 2006 and
fiscal_wk between 31 and 41 then on_hand
when rapid_refresh_date = '2006-09-04' and b.reporting_year = 2006 and
fiscal_wk between 32 and 41 then on_hand
when rapid_refresh_date = '2006-09-11' and b.reporting_year = 2006 and
fiscal_wk between 33 and 41 then on_hand
when rapid_refresh_date = '2006-09-18' and b.reporting_year = 2006 and
fiscal_wk between 34 and 41 then on_hand
when rapid_refresh_date = '2006-09-25' and b.reporting_year = 2006 and
fiscal_wk between 35 and 41 then on_hand
when rapid_refresh_date = '2006-10-02' and b.reporting_year = 2006 and
fiscal_wk between 36 and 41 then on_hand
when rapid_refresh_date = '2006-10-09' and b.reporting_year = 2006 and
fiscal_wk between 37 and 41 then on_hand
when rapid_refresh_date = '2006-10-16' and b.reporting_year = 2006 and
fiscal_wk between 38 and 41 then on_hand
when rapid_refresh_date = '2006-10-23' and b.reporting_year = 2006 and
fiscal_wk between 39 and 41 then on_hand
when rapid_refresh_date = '2006-10-30' and b.reporting_year = 2006 and
fiscal_wk between 40 and 41 then on_hand
when rapid_refresh_date = '2006-11-06' and b.reporting_year = 2006 and
fiscal_wk between 41 and 41 then on_hand
when rapid_refresh_date = '2006-11-13' and b.reporting_year = 2006 and
fiscal_wk between 42 and 41 then on_hand
else 0 end) [2006PostOnHand],
when rapid_refresh_date = '2006-07-10' and b.reporting_year = 2005 and
fiscal_wk in (11,12,13,14,15,16,17,18,19,20,21,22,23) then qty_sold
when rapid_refresh_date = '2006-07-17' and b.reporting_year = 2005 and
fiscal_wk in (12,13,14,15,16,17,18,19,20,21,22,23,24) then qty_sold
when rapid_refresh_date = '2006-07-24' and b.reporting_year = 2005 and
fiscal_wk in (13,14,15,16,17,18,19,20,21,22,23,24,25) then qty_sold
when rapid_refresh_date = '2006-07-31' and b.reporting_year = 2005 and
fiscal_wk in (14,15,16,17,18,19,20,21,22,23,24,25,26) then qty_sold
when rapid_refresh_date = '2006-08-07' and b.reporting_year = 2005 and
fiscal_wk in (15,16,17,18,19,20,21,22,23,24,25,26,27) then qty_sold
when rapid_refresh_date = '2006-08-14' and b.reporting_year = 2005 and
fiscal_wk in (16,17,18,19,20,21,22,23,24,25,26,27,28) then qty_sold
when rapid_refresh_date = '2006-08-21' and b.reporting_year = 2005 and
fiscal_wk in (17,18,19,20,21,22,23,24,25,26,27,28,29) then qty_sold
when rapid_refresh_date = '2006-08-28' and b.reporting_year = 2005 and
fiscal_wk in (18,19,20,21,22,23,24,25,26,27,28,29,30) then qty_sold
when rapid_refresh_date = '2006-09-04' and b.reporting_year = 2005 and
fiscal_wk in (19,20,21,22,23,24,25,26,27,28,29,30,31) then qty_sold
when rapid_refresh_date = '2006-09-11' and b.reporting_year = 2005 and
fiscal_wk in (20,21,22,23,24,25,26,27,28,29,30,31,32) then qty_sold
when rapid_refresh_date = '2006-09-18' and b.reporting_year = 2005 and
fiscal_wk in (21,22,23,24,25,26,27,28,29,30,31,32,33) then qty_sold
when rapid_refresh_date = '2006-09-25' and b.reporting_year = 2005 and
fiscal_wk in (22,23,24,25,26,27,28,29,30,31,32,33,34)then qty_sold
when rapid_refresh_date = '2006-10-02' and b.reporting_year = 2005 and
fiscal_wk in (23,24,25,26,27,28,29,30,31,32,33,34,35)then qty_sold
when rapid_refresh_date = '2006-10-09' and b.reporting_year = 2005 and
fiscal_wk in (24,25,26,27,28,29,30,31,32,33,34,35,36)then qty_sold
when rapid_refresh_date = '2006-10-16' and b.reporting_year = 2005 and
fiscal_wk in (25,26,27,28,29,30,31,32,33,34,35,36,37)then qty_sold
when rapid_refresh_date = '2006-10-23' and b.reporting_year = 2005 and
fiscal_wk in (26,27,28,29,30,31,32,33,34,35,36,37,38)then qty_sold
when rapid_refresh_date = '2006-10-30' and b.reporting_year = 2005 and
fiscal_wk in (27,28,29,30,31,32,33,34,35,36,37,38,39)then qty_sold
when rapid_refresh_date = '2006-11-06' and b.reporting_year = 2005 and
fiscal_wk in (28,29,30,31,32,33,34,35,36,37,38,39,40)then qty_sold
when rapid_refresh_date = '2006-11-13' and b.reporting_year = 2005 and
fiscal_wk in (29,30,31,32,33,34,35,36,37,38,39,40,41)then qty_sold
else 0 end) [2005PreUnits],
sum(case
when rapid_refresh_date = '2006-07-10' and b.reporting_year = 2005 and
fiscal_wk in (11,12,13,14,15,16,17,18,19,20,21,22,23) then currency_sold
when rapid_refresh_date = '2006-07-17' and b.reporting_year = 2005 and
fiscal_wk in (12,13,14,15,16,17,18,19,20,21,22,23,24) then currency_sold
when rapid_refresh_date = '2006-07-24' and b.reporting_year = 2005 and
fiscal_wk in (13,14,15,16,17,18,19,20,21,22,23,24,25) then currency_sold
when rapid_refresh_date = '2006-07-31' and b.reporting_year = 2005 and
fiscal_wk in (14,15,16,17,18,19,20,21,22,23,24,25,26) then currency_sold
when rapid_refresh_date = '2006-08-07' and b.reporting_year = 2005 and
fiscal_wk in (15,16,17,18,19,20,21,22,23,24,25,26,27) then currency_sold
when rapid_refresh_date = '2006-08-14' and b.reporting_year = 2005 and
fiscal_wk in (16,17,18,19,20,21,22,23,24,25,26,27,28) then currency_sold
when rapid_refresh_date = '2006-08-21' and b.reporting_year = 2005 and
fiscal_wk in (17,18,19,20,21,22,23,24,25,26,27,28,29) then currency_sold
when rapid_refresh_date = '2006-08-28' and b.reporting_year = 2005 and
fiscal_wk in (18,19,20,21,22,23,24,25,26,27,28,29,30) then currency_sold
when rapid_refresh_date = '2006-09-04' and b.reporting_year = 2005 and
fiscal_wk in (19,20,21,22,23,24,25,26,27,28,29,30,31) then currency_sold
when rapid_refresh_date = '2006-09-11' and b.reporting_year = 2005 and
fiscal_wk in (20,21,22,23,24,25,26,27,28,29,30,31,32) then currency_sold
when rapid_refresh_date = '2006-09-18' and b.reporting_year = 2005 and
fiscal_wk in (21,22,23,24,25,26,27,28,29,30,31,32,33) then currency_sold
when rapid_refresh_date = '2006-09-25' and b.reporting_year = 2005 and
fiscal_wk in (22,23,24,25,26,27,28,29,30,31,32,33,34)then currency_sold
when rapid_refresh_date = '2006-10-02' and b.reporting_year = 2005 and
fiscal_wk in (23,24,25,26,27,28,29,30,31,32,33,34,35)then currency_sold
when rapid_refresh_date = '2006-10-09' and b.reporting_year = 2005 and
fiscal_wk in (24,25,26,27,28,29,30,31,32,33,34,35,36)then currency_sold
when rapid_refresh_date = '2006-10-16' and b.reporting_year = 2005 and
fiscal_wk in (25,26,27,28,29,30,31,32,33,34,35,36,37)then currency_sold
when rapid_refresh_date = '2006-10-23' and b.reporting_year = 2005 and
fiscal_wk in (26,27,28,29,30,31,32,33,34,35,36,37,38)then currency_sold
when rapid_refresh_date = '2006-10-30' and b.reporting_year = 2005 and
fiscal_wk in (27,28,29,30,31,32,33,34,35,36,37,38,39)then currency_sold
when rapid_refresh_date = '2006-11-06' and b.reporting_year = 2005 and
fiscal_wk in (28,29,30,31,32,33,34,35,36,37,38,39,40)then currency_sold
when rapid_refresh_date = '2006-11-13' and b.reporting_year = 2005 and
fiscal_wk in (29,30,31,32,33,34,35,36,37,38,39,40,41)then currency_sold
else 0 end) [2005PrePOS],
when rapid_refresh_date = '2006-07-10' and b.reporting_year = 2005 and
fiscal_wk between 24 and 41 then qty_sold
when rapid_refresh_date = '2006-07-17' and b.reporting_year = 2005 and
fiscal_wk between 25 and 41 then qty_sold
when rapid_refresh_date = '2006-07-24' and b.reporting_year = 2005 and
fiscal_wk between 26 and 41 then qty_sold
when rapid_refresh_date = '2006-07-31' and b.reporting_year = 2005 and
fiscal_wk between 27 and 41 then qty_sold
when rapid_refresh_date = '2006-08-07' and b.reporting_year = 2005 and
fiscal_wk between 28 and 41 then qty_sold
when rapid_refresh_date = '2006-08-14' and b.reporting_year = 2005 and
fiscal_wk between 29 and 41 then qty_sold
when rapid_refresh_date = '2006-08-21' and b.reporting_year = 2005 and
fiscal_wk between 30 and 41 then qty_sold
when rapid_refresh_date = '2006-08-28' and b.reporting_year = 2005 and
fiscal_wk between 31 and 41 then qty_sold
when rapid_refresh_date = '2006-09-04' and b.reporting_year = 2005 and
fiscal_wk between 32 and 41 then qty_sold
when rapid_refresh_date = '2006-09-11' and b.reporting_year = 2005 and
fiscal_wk between 33 and 41 then qty_sold
when rapid_refresh_date = '2006-09-18' and b.reporting_year = 2005 and
fiscal_wk between 34 and 41 then qty_sold
when rapid_refresh_date = '2006-09-25' and b.reporting_year = 2005 and
fiscal_wk between 35 and 41 then qty_sold
when rapid_refresh_date = '2006-10-02' and b.reporting_year = 2005 and
fiscal_wk between 36 and 41 then qty_sold
when rapid_refresh_date = '2006-10-09' and b.reporting_year = 2005 and
fiscal_wk between 37 and 41 then qty_sold
when rapid_refresh_date = '2006-10-16' and b.reporting_year = 2005 and
fiscal_wk between 38 and 41 then qty_sold
when rapid_refresh_date = '2006-10-23' and b.reporting_year = 2005 and
fiscal_wk between 39 and 41 then qty_sold
when rapid_refresh_date = '2006-10-30' and b.reporting_year = 2005 and
fiscal_wk between 40 and 41 then qty_sold
when rapid_refresh_date = '2006-11-06' and b.reporting_year = 2005 and
fiscal_wk between 41 and 41 then qty_sold
when rapid_refresh_date = '2006-11-13' and b.reporting_year = 2005 and
fiscal_wk between 42 and 41 then qty_sold
else 0 end) [2005PostUnits],
fiscal_wk between 24 and 41 then currency_sold
when rapid_refresh_date = '2006-07-17' and b.reporting_year = 2005 and
fiscal_wk between 25 and 41 then currency_sold
when rapid_refresh_date = '2006-07-24' and b.reporting_year = 2005 and
fiscal_wk between 26 and 41 then currency_sold
when rapid_refresh_date = '2006-07-31' and b.reporting_year = 2005 and
fiscal_wk between 27 and 41 then currency_sold
when rapid_refresh_date = '2006-08-07' and b.reporting_year = 2005 and
fiscal_wk between 28 and 41 then currency_sold
when rapid_refresh_date = '2006-08-14' and b.reporting_year = 2005 and
fiscal_wk between 29 and 41 then currency_sold
when rapid_refresh_date = '2006-08-21' and b.reporting_year = 2005 and
fiscal_wk between 30 and 41 then currency_sold
when rapid_refresh_date = '2006-08-28' and b.reporting_year = 2005 and
fiscal_wk between 31 and 41 then currency_sold
when rapid_refresh_date = '2006-09-04' and b.reporting_year = 2005 and
fiscal_wk between 32 and 41 then currency_sold
when rapid_refresh_date = '2006-09-11' and b.reporting_year = 2005 and
fiscal_wk between 33 and 41 then currency_sold
when rapid_refresh_date = '2006-09-18' and b.reporting_year = 2005 and
fiscal_wk between 34 and 41 then currency_sold
when rapid_refresh_date = '2006-09-25' and b.reporting_year = 2005 and
fiscal_wk between 35 and 41 then currency_sold
when rapid_refresh_date = '2006-10-02' and b.reporting_year = 2005 and
fiscal_wk between 36 and 41 then currency_sold
when rapid_refresh_date = '2006-10-09' and b.reporting_year = 2005 and
fiscal_wk between 37 and 41 then currency_sold
when rapid_refresh_date = '2006-10-16' and b.reporting_year = 2005 and
fiscal_wk between 38 and 41 then currency_sold
when rapid_refresh_date = '2006-10-23' and b.reporting_year = 2005 and
fiscal_wk between 39 and 41 then currency_sold
when rapid_refresh_date = '2006-10-30' and b.reporting_year = 2005 and
fiscal_wk between 40 and 41 then currency_sold
when rapid_refresh_date = '2006-11-06' and b.reporting_year = 2005 and
fiscal_wk between 41 and 41 then currency_sold
when rapid_refresh_date = '2006-11-13' and b.reporting_year = 2005 and
fiscal_wk between 42 and 41 then currency_sold
else 0 end) [2005PostPOS],
when rapid_refresh_date = '2006-07-10' and b.reporting_year = 2006 and
fiscal_wk in (11,12,13,14,15,16,17,18,19,20,21,22,23) then qty_sold
when rapid_refresh_date = '2006-07-17' and b.reporting_year = 2006 and
fiscal_wk in (12,13,14,15,16,17,18,19,20,21,22,23,24) then qty_sold
when rapid_refresh_date = '2006-07-24' and b.reporting_year = 2006 and
fiscal_wk in (13,14,15,16,17,18,19,20,21,22,23,24,25) then qty_sold
when rapid_refresh_date = '2006-07-31' and b.reporting_year = 2006 and
fiscal_wk in (14,15,16,17,18,19,20,21,22,23,24,25,26) then qty_sold
when rapid_refresh_date = '2006-08-07' and b.reporting_year = 2006 and
fiscal_wk in (15,16,17,18,19,20,21,22,23,24,25,26,27) then qty_sold
when rapid_refresh_date = '2006-08-14' and b.reporting_year = 2006 and
fiscal_wk in (16,17,18,19,20,21,22,23,24,25,26,27,28) then qty_sold
when rapid_refresh_date = '2006-08-21' and b.reporting_year = 2006 and
fiscal_wk in (17,18,19,20,21,22,23,24,25,26,27,28,29) then qty_sold
when rapid_refresh_date = '2006-08-28' and b.reporting_year = 2006 and
fiscal_wk in (18,19,20,21,22,23,24,25,26,27,28,29,30) then qty_sold
when rapid_refresh_date = '2006-09-04' and b.reporting_year = 2006 and
fiscal_wk in (19,20,21,22,23,24,25,26,27,28,29,30,31) then qty_sold
when rapid_refresh_date = '2006-09-11' and b.reporting_year = 2006 and
fiscal_wk in (20,21,22,23,24,25,26,27,28,29,30,31,32) then qty_sold
when rapid_refresh_date = '2006-09-18' and b.reporting_year = 2006 and
fiscal_wk in (21,22,23,24,25,26,27,28,29,30,31,32,33) then qty_sold
when rapid_refresh_date = '2006-09-25' and b.reporting_year = 2006 and
fiscal_wk in (22,23,24,25,26,27,28,29,30,31,32,33,34)then qty_sold
when rapid_refresh_date = '2006-10-02' and b.reporting_year = 2006 and
fiscal_wk in (23,24,25,26,27,28,29,30,31,32,33,34,35)then qty_sold
when rapid_refresh_date = '2006-10-09' and b.reporting_year = 2006 and
fiscal_wk in (24,25,26,27,28,29,30,31,32,33,34,35,36)then qty_sold
when rapid_refresh_date = '2006-10-16' and b.reporting_year = 2006 and
fiscal_wk in (25,26,27,28,29,30,31,32,33,34,35,36,37)then qty_sold
when rapid_refresh_date = '2006-10-23' and b.reporting_year = 2006 and
fiscal_wk in (26,27,28,29,30,31,32,33,34,35,36,37,38)then qty_sold
when rapid_refresh_date = '2006-10-30' and b.reporting_year = 2006 and
fiscal_wk in (27,28,29,30,31,32,33,34,35,36,37,38,39)then qty_sold
when rapid_refresh_date = '2006-11-06' and b.reporting_year = 2006 and
fiscal_wk in (28,29,30,31,32,33,34,35,36,37,38,39,40)then qty_sold
when rapid_refresh_date = '2006-11-13' and b.reporting_year = 2006 and
fiscal_wk in (29,30,31,32,33,34,35,36,37,38,39,40,41)then qty_sold
else 0 end) [2006PreUnit],
fiscal_wk in (11,12,13,14,15,16,17,18,19,20,21,22,23) then currency_sold
when rapid_refresh_date = '2006-07-17' and b.reporting_year = 2006 and
fiscal_wk in (12,13,14,15,16,17,18,19,20,21,22,23,24) then currency_sold
when rapid_refresh_date = '2006-07-24' and b.reporting_year = 2006 and
fiscal_wk in (13,14,15,16,17,18,19,20,21,22,23,24,25) then currency_sold
when rapid_refresh_date = '2006-07-31' and b.reporting_year = 2006 and
fiscal_wk in (14,15,16,17,18,19,20,21,22,23,24,25,26) then currency_sold
when rapid_refresh_date = '2006-08-07' and b.reporting_year = 2006 and
fiscal_wk in (15,16,17,18,19,20,21,22,23,24,25,26,27) then currency_sold
when rapid_refresh_date = '2006-08-14' and b.reporting_year = 2006 and
fiscal_wk in (16,17,18,19,20,21,22,23,24,25,26,27,28) then currency_sold
when rapid_refresh_date = '2006-08-21' and b.reporting_year = 2006 and
fiscal_wk in (17,18,19,20,21,22,23,24,25,26,27,28,29) then currency_sold
when rapid_refresh_date = '2006-08-28' and b.reporting_year = 2006 and
fiscal_wk in (18,19,20,21,22,23,24,25,26,27,28,29,30) then currency_sold
when rapid_refresh_date = '2006-09-04' and b.reporting_year = 2006 and
fiscal_wk in (19,20,21,22,23,24,25,26,27,28,29,30,31) then currency_sold
when rapid_refresh_date = '2006-09-11' and b.reporting_year = 2006 and
fiscal_wk in (20,21,22,23,24,25,26,27,28,29,30,31,32) then currency_sold
when rapid_refresh_date = '2006-09-18' and b.reporting_year = 2006 and
fiscal_wk in (21,22,23,24,25,26,27,28,29,30,31,32,33) then currency_sold
when rapid_refresh_date = '2006-09-25' and b.reporting_year = 2006 and
fiscal_wk in (22,23,24,25,26,27,28,29,30,31,32,33,34)then currency_sold
when rapid_refresh_date = '2006-10-02' and b.reporting_year = 2006 and
fiscal_wk in (23,24,25,26,27,28,29,30,31,32,33,34,35)then currency_sold
when rapid_refresh_date = '2006-10-09' and b.reporting_year = 2006 and
fiscal_wk in (24,25,26,27,28,29,30,31,32,33,34,35,36)then currency_sold
when rapid_refresh_date = '2006-10-16' and b.reporting_year = 2006 and
fiscal_wk in (25,26,27,28,29,30,31,32,33,34,35,36,37)then currency_sold
when rapid_refresh_date = '2006-10-23' and b.reporting_year = 2006 and
fiscal_wk in (26,27,28,29,30,31,32,33,34,35,36,37,38)then currency_sold
when rapid_refresh_date = '2006-10-30' and b.reporting_year = 2006 and
fiscal_wk in (27,28,29,30,31,32,33,34,35,36,37,38,39)then currency_sold
when rapid_refresh_date = '2006-11-06' and b.reporting_year = 2006 and
fiscal_wk in (28,29,30,31,32,33,34,35,36,37,38,39,40)then currency_sold
when rapid_refresh_date = '2006-11-13' and b.reporting_year = 2006 and
fiscal_wk in (29,30,31,32,33,34,35,36,37,38,39,40,41)then currency_sold
else 0 end) [2006PrePOS],
fiscal_wk between 24 and 41 then qty_sold
when rapid_refresh_date = '2006-07-17' and b.reporting_year = 2006 and
fiscal_wk between 25 and 41 then qty_sold
when rapid_refresh_date = '2006-07-24' and b.reporting_year = 2006 and
fiscal_wk between 26 and 41 then qty_sold
when rapid_refresh_date = '2006-07-31' and b.reporting_year = 2006 and
fiscal_wk between 27 and 41 then qty_sold
when rapid_refresh_date = '2006-08-07' and b.reporting_year = 2006 and
fiscal_wk between 28 and 41 then qty_sold
when rapid_refresh_date = '2006-08-14' and b.reporting_year = 2006 and
fiscal_wk between 29 and 41 then qty_sold
when rapid_refresh_date = '2006-08-21' and b.reporting_year = 2006 and
fiscal_wk between 30 and 41 then qty_sold
when rapid_refresh_date = '2006-08-28' and b.reporting_year = 2006 and
fiscal_wk between 31 and 41 then qty_sold
when rapid_refresh_date = '2006-09-04' and b.reporting_year = 2006 and
fiscal_wk between 32 and 41 then qty_sold
when rapid_refresh_date = '2006-09-11' and b.reporting_year = 2006 and
fiscal_wk between 33 and 41 then qty_sold
when rapid_refresh_date = '2006-09-18' and b.reporting_year = 2006 and
fiscal_wk between 34 and 41 then qty_sold
when rapid_refresh_date = '2006-09-25' and b.reporting_year = 2006 and
fiscal_wk between 35 and 41 then qty_sold
when rapid_refresh_date = '2006-10-02' and b.reporting_year = 2006 and
fiscal_wk between 36 and 41 then qty_sold
when rapid_refresh_date = '2006-10-09' and b.reporting_year = 2006 and
fiscal_wk between 37 and 41 then qty_sold
when rapid_refresh_date = '2006-10-16' and b.reporting_year = 2006 and
fiscal_wk between 38 and 41 then qty_sold
when rapid_refresh_date = '2006-10-23' and b.reporting_year = 2006 and
fiscal_wk between 39 and 41 then qty_sold
when rapid_refresh_date = '2006-10-30' and b.reporting_year = 2006 and
fiscal_wk between 40 and 41 then qty_sold
when rapid_refresh_date = '2006-11-06' and b.reporting_year = 2006 and
fiscal_wk between 41 and 41 then qty_sold
when rapid_refresh_date = '2006-11-13' and b.reporting_year = 2006 and
fiscal_wk between 42 and 41 then qty_sold
else 0 end) [2006PostUnits],
fiscal_wk between 24 and 41 then currency_sold
when rapid_refresh_date = '2006-07-17' and b.reporting_year = 2006 and
fiscal_wk between 25 and 41 then currency_sold
when rapid_refresh_date = '2006-07-24' and b.reporting_year = 2006 and
fiscal_wk between 26 and 41 then currency_sold
when rapid_refresh_date = '2006-07-31' and b.reporting_year = 2006 and
fiscal_wk between 27 and 41 then currency_sold
when rapid_refresh_date = '2006-08-07' and b.reporting_year = 2006 and
fiscal_wk between 28 and 41 then currency_sold
when rapid_refresh_date = '2006-08-14' and b.reporting_year = 2006 and
fiscal_wk between 29 and 41 then currency_sold
when rapid_refresh_date = '2006-08-21' and b.reporting_year = 2006 and
fiscal_wk between 30 and 41 then currency_sold
when rapid_refresh_date = '2006-08-28' and b.reporting_year = 2006 and
fiscal_wk between 31 and 41 then currency_sold
when rapid_refresh_date = '2006-09-04' and b.reporting_year = 2006 and
fiscal_wk between 32 and 41 then currency_sold
when rapid_refresh_date = '2006-09-11' and b.reporting_year = 2006 and
fiscal_wk between 33 and 41 then currency_sold
when rapid_refresh_date = '2006-09-18' and b.reporting_year = 2006 and
fiscal_wk between 34 and 41 then currency_sold
when rapid_refresh_date = '2006-09-25' and b.reporting_year = 2006 and
fiscal_wk between 35 and 41 then currency_sold
when rapid_refresh_date = '2006-10-02' and b.reporting_year = 2006 and
fiscal_wk between 36 and 41 then currency_sold
when rapid_refresh_date = '2006-10-09' and b.reporting_year = 2006 and
fiscal_wk between 37 and 41 then currency_sold
when rapid_refresh_date = '2006-10-16' and b.reporting_year = 2006 and
fiscal_wk between 38 and 41 then currency_sold
when rapid_refresh_date = '2006-10-23' and b.reporting_year = 2006 and
fiscal_wk between 39 and 41 then currency_sold
when rapid_refresh_date = '2006-10-30' and b.reporting_year = 2006 and
fiscal_wk between 40 and 41 then currency_sold
when rapid_refresh_date = '2006-11-06' and b.reporting_year = 2006 and
fiscal_wk between 41 and 41 then currency_sold
when rapid_refresh_date = '2006-11-13' and b.reporting_year = 2006 and
fiscal_wk between 42 and 41 then currency_sold
else 0 end) [2006PostPOS]
from
depot_pos_detail p
inner join depot_stores s on s.idx_store = p.idx_store
inner join depot_items i on i.idx_item = p.idx_item
inner join depot_buckets b on b.date_start = p.date_start
left outer join depot_class_codes c on c.hd_class = i.hd_class and c.hd_sub_class = i.hd_sub_class
Cross Join (Select max(Date_Start) MaxDate from Depot_POS_Detail) d
store_nbr in (
6683,146,105,145,257,918,652,933,1052,262,904,129,6615,633,151,1202,132,116,645,
930
)
and i.hd_class = 5
and b.reporting_year in (2005,2006)
d.maxdate,
hd_byo_nbr,
store_nbr,
store_name,
i.hd_sub_class,
hd_sub_class_name,
hd_sku,
rapid_refresh_date,
rr_week
November 23, 2006 at 8:00 am
This was removed by the editor as SPAM
November 23, 2006 at 10:10 am
Use a table to store your periods/dates/etc and join with it. That should get you a long way there.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply