|
|
Posted by Ed Murphy on 10/17/07 21:44
imani_technology_spam@yahoo.com wrote:
> We have this basic SELECT statement:
>
> SELECT product_id, we_date, sum(demand_units)
> FROM weekly_transactions
> WHERE demand_units > 0
> GROUP BY product_id, we_date
> ORDER BY product_id, we_date
>
> However, for each Product and WE_DATE, we also want the demand units
> for the previous 10 weeks. So far week ending 9/23/2007, we want the
> demand_units for that week PLUS the demand_units for the previous 10
> weeks. I have NOT idea how to pull this off! Can anyone out there
> help me?
create view v_weekly_totals as
select product_id, we_date, sum(demand_units) demand_total
from weekly_transactions
where demand_units > 0
group by product_id, we_date
go
select wc.product_id, wc.we_date,
wc.demand_total wc_demand_total,
wp1.demand_total wp1_demand_total,
wp2.demand_total wp2_demand_total,
wp3.demand_total wp3_demand_total,
wp4.demand_total wp4_demand_total,
wp5.demand_total wp5_demand_total,
wp6.demand_total wp6_demand_total,
wp7.demand_total wp7_demand_total,
wp8.demand_total wp8_demand_total,
wp9.demand_total wp9_demand_total,
wp10.demand_total wp10_demand_total
from v_weekly_totals wc
left join v_weekly_totals wp1
on wp1.product_id = wc.product_id
and wp1.we_date = dateadd(week,-1,wc.we_date)
left join v_weekly_totals wp2
on wp2.product_id = wc.product_id
and wp2.we_date = dateadd(week,-2,wc.we_date)
-- similarly for wp3 through wp10
order by wc.product_id, wc.we_date
Navigation:
[Reply to this message]
|