Re: T-SQL Challenge
- From: Hugo Kornelis <hugo@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 17 Oct 2007 23:32:17 +0200
On Wed, 17 Oct 2007 12:14:00 -0700, imani_technology_spam@xxxxxxxxx
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
.
- References:
- T-SQL Challenge
- From: imani_technology_spam@xxxxxxxxx
- T-SQL Challenge
- Prev by Date: Re: A suitable SQL Server book for my goals
- Next by Date: Re: define unique keys
- Previous by thread: T-SQL Challenge
- Next by thread: Re: T-SQL Challenge
- Index(es):
Relevant Pages
|
