Posted by imani_technology_spam@yahoo.com on 10/18/07 19:21

On Oct 17, 2:44 pm, Ed Murphy <emurph...@socal.rr.com> wrote:
> imani_technology_s...@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

Thanks!

[Back to original message]