Posted by --CELKO-- on 10/19/07 03:37

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

[Back to original message]