T-SQL Challenge

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?

Re: T-SQL Challenge

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

Re: T-SQL Challenge

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

Re: T-SQL Challenge

On Oct 17, 2:44 pm, Ed Murphy 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!

Re: T-SQL Challenge

>>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 ; <<

Where is your DDL? What is a week_date [weeks and dates are different
units of measurement]? Did you mean to use the ISO-8601 Standard week-
within-year or what? Why is a demand of zero not possible?

>> 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 [sic: '2007-09-23' as per ISO-8601 and SQL standards!!], we want the demand_units for that week PLUS the demand_units for the previous 10 weeks. <<

Are ten previous weeks on the same row? Or is the ten-week total on
the same row? Are the ten totals on separate rows? Where is the
sample data that you should have posted with the DDL?

My guess, based on the lack of clear specs, no sample data and no DDL
is that you could use a report range table which has adjustments to
your fiscal calendar definition of a week.