Posted by Hugo Kornelis on 10/17/07 21:32

On Wed, 17 Oct 2007 12:14:00 -0700, 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?

Hi imani_technology_spam,

SELECT a.product_id, a.we_date, SUM(b.demand_units)
FROM weekly_transactions AS a
INNER JOIN weekly_transactions AS b
ON b.product_id = a.product_id
AND b.we_date BETWEEN DATEADD(week, -10, a.we_date)
AND a.we_date
GROUP BY product_id, we_date
ORDER BY product_id, we_date;

(untested - see www.aspfaq.com/5006 if you prefer a tested reply).

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[Back to original message]