Re: Efficient query for this year/last year/two years ago

> When I first took a crack at this I had loads of nested subqueries,
> self-joins, etc. to accomplish the cumulative sums. Then I discovered
> the analytical windowing queries and found that the cumulative sums by
> year and quarter and such could be delivered in the same row very
> easily:
>
> create table sales_by_week_cume as
> select t2.fiscal_year_week,
> sales as WTD,
> sum(sales) over(
> partition by fiscal_month
> order by t2.fiscal_year_week
> range between fiscal_month preceding and current row
> ) as Sales_MTD
> from sales_by_week t1
> inner join fiscal_weeks t2
> on t1.fiscal_year_week = t2.fiscal_year_week
> order by fiscal_year_week;
>

You ought to re-think the whole thing ... have you looked at the results from your first query? Is SALES_MTD really what you want ... shouldn't the MTD for 200403 be 2400? Received on Thu Nov 09 2006 - 12:23:13 CST