Developer Forum

reetings, everyone. I'm hoping someone has a moment to assist me in
finding an efficient solution to deliver a table that contains ytd,
qtd, mtd and wtd sales for this year, the corresponding period the year
before, and the corresponding period the year before that, at the
weekly grain, using a weekly sales table and dates table as sources.

Assume this table for our dates

create table fiscal_weeks (
fiscal_year_week number PRIMARY KEY,
fiscal_year number,
fiscal_month number,
fiscal_year_week_LY number
);

Assume this sales table:

create table sales_by_week (
fiscal_year_week number primary key,
sales number
);

Here's some sample data:

insert into fiscal_weeks values (200601, 2006, 1, 200501);
insert into fiscal_weeks values (200602, 2006, 1, 200502);
insert into fiscal_weeks values (200603, 2006, 1, 200503);
insert into fiscal_weeks values (200501, 2005, 1, 200401);
insert into fiscal_weeks values (200502, 2005, 1, 200402);
insert into fiscal_weeks values (200503, 2005, 1, 200403);
insert into fiscal_weeks values (200401, 2004, 1, 200301);
insert into fiscal_weeks values (200402, 2004, 1, 200302);
insert into fiscal_weeks values (200403, 2004, 1, 200303);
insert into sales_by_week values (200601, 100);
insert into sales_by_week values (200602, 200);
insert into sales_by_week values (200603, 300);
insert into sales_by_week values (200501, 400);
insert into sales_by_week values (200502, 500);
insert into sales_by_week values (200503, 600);
insert into sales_by_week values (200401, 700);
insert into sales_by_week values (200402, 800);
insert into sales_by_week values (200403, 900);
commit;


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;

The windowing functions are repeated for each of the cume periods I
need (QTD and YTD). But limiting just to MTD for the sake of this
example, this essentially gives me a set that is my weekly sales table
with cumulative, window-based sums, but I still need to get this year,
last year and two years ago in the same row. To do that I did the
following, which works, but is very costly and slow:

select ty.fiscal_year_week, ty.ty_sales_mtd, ly.ly_sales_mtd,
lly.lly_sales_mtd
from (
select t2.fiscal_year_week, t2.fiscal_year_week_ly, sales_mtd
as ty_sales_mtd
from sales_by_week_cume t1
inner join fiscal_weeks t2
on t1.fiscal_year_week = t2.fiscal_year_week
where t2.fiscal_year = 2006
) TY
inner join (
select t2.fiscal_year_week, t2.fiscal_year_week_ly, sales_mtd
as ly_sales_mtd
from sales_by_week_cume t1
inner join fiscal_weeks t2
on t1.fiscal_year_week = t2.fiscal_year_week
where t2.fiscal_year = 2005
) LY
on ty.fiscal_year_week_ly = ly.fiscal_year

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?

erch wrote:

The create and insert statements help, a couple more to provide
interesting data.
insert into fiscal_weeks values (200605, 2006, 2, 200505);
insert into sales_by_week values (200605, 870);
insert into fiscal_weeks values (200505, 2005, 2, 200405);
insert into sales_by_week values (200505, 1500);
insert into fiscal_weeks values (200405, 2004, 2, 200305);
insert into sales_by_week values (200405, 1230);
insert into fiscal_weeks values (200626, 2006, 7, 200526);
insert into sales_by_week values (200626, 999);
insert into fiscal_weeks values (200526, 2005, 7, 200426);
insert into sales_by_week values (200526, 444);
insert into fiscal_weeks values (200426, 2004, 7, 200326);
insert into sales_by_week values (200426, 333);

The example below does not use analytical functions.
SELECT
MAX(FISCAL_YEAR_WEEK) FISCAL_YEAR_WEEK,
MAX(FISCAL_YEAR) FISCAL_YEAR,
MAX(FISCAL_MONTH) FISCAL_MONTH,
FISCAL_WEEK_NUM,
MAX(DECODE(FISCAL_YEAR,TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')),SALES,0))
CUR_W_SALES,

MAX(DECODE(FISCAL_YEAR,TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE,-12),'YYYY')),SALES,0))
PRE_W_SALES,

MAX(DECODE(FISCAL_YEAR,TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE,-24),'YYYY')),SALES,0))
PRE2_W_SALES,

MAX(DECODE(FISCAL_YEAR,TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')),YTD_SALES,0))
CUR_YTD_SALES,

MAX(DECODE(FISCAL_YEAR,TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE,-12),'YYYY')),YTD_SALES,0))
PRE_YTD_SALES,

MAX(DECODE(FISCAL_YEAR,TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE,-24),'YYYY')),YTD_SALES,0))
PRE2_YTD_SALES,

MAX(DECODE(FISCAL_YEAR,TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')),MTD_SALES,0))
CUR_MTD_SALES,

MAX(DECODE(FISCAL_YEAR,TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE,-12),'YYYY')),MTD_SALES,0))
PRE_MTD_SALES,

MAX(DECODE(FISCAL_YEAR,TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE,-24),'YYYY')),MTD_SALES,0))
PRE2_MTD_SALES,

MAX(DECODE(FISCAL_YEAR,TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')),QTD_SALES,0))
CUR_QTD_SALES,

MAX(DECODE(FISCAL_YEAR,TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE,-12),'YYYY')),QTD_SALES,0))
PRE_QTD_SALES,

MAX(DECODE(FISCAL_YEAR,TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE,-24),'YYYY')),QTD_SALES,0))
PRE2_QTD_SALES
FROM
(SELECT
FW.FISCAL_YEAR_WEEK,
FW.FISCAL_YEAR,
FW.FISCAL_MONTH,
FW.FISCAL_WEEK_NUM,
FW.SALES,
SUM(SW.SALES) YTD_SALES,
SUM(DECODE(SIGN(SW.FISCAL_MONTH-FW.FISCAL_MONTH),0,SW.SALES))
MTD_SALES,

SUM(DECODE(SIGN(TRUNC(SW.FISCAL_MONTH/3)-TRUNC(FW.FISCAL_MONTH/3)),0,SW.SALES))
QTD_SALES
FROM
(SELECT
FW.FISCAL_YEAR_WEEK,
FW.FISCAL_YEAR,
FW.FISCAL_MONTH,
TO_NUMBER(SUBSTR(TO_CHAR(SW.FISCAL_YEAR_WEEK),5)) FISCAL_WEEK_NUM,
SW.SALES
FROM
FISCAL_WEEKS FW,
SALES_BY_WEEK SW
WHERE
FW.FISCAL_YEAR_WEEK=SW.FISCAL_YEAR_WEEK) FW,
(SELECT
SW.FISCAL_YEAR_WEEK,
FW.FISCAL_YEAR FISCAL_YEAR_NUM,
TO_NUMBER(SUBSTR(TO_CHAR(SW.FISCAL_YEAR_WEEK),5)) FISCAL_WEEK_NUM,
FW.FISCAL_MONTH,
SW.SALES
FROM
FISCAL_WEEKS FW,
SALES_BY_WEEK SW
WHERE
FW.FISCAL_YEAR_WEEK=SW.FISCAL_YEAR_WEEK) SW
WHERE
FW.FISCAL_YEAR=SW.FISCAL_YEAR_NUM
AND FW.FISCAL_WEEK_NUM>=SW.FISCAL_WEEK_NUM
GROUP BY
FW.FISCAL_YEAR_WEEK,
FW.FISCAL_YEAR,
FW.FISCAL_MONTH,
FW.FISCAL_WEEK_NUM,
FW.SALES)
GROUP BY
FISCAL_WEEK_NUM
ORDER BY
1;

Output:
FISCAL_YEAR_WEEK
FISCAL_YEAR
FISCAL_MONTH
FISCAL_WEEK_NUM
CUR_W_SALES
PRE_W_SALES
PRE2_W_SALES
CUR_YTD_SALES
PRE_YTD_SALES
PRE2_YTD_SALES
CUR_MTD_SALES
PRE_MTD_SALES
PRE2_MTD_SALES
CUR_Q

the whole thing ... have you looked at the

Well yes, it certainly should be. I don't know what I've got wrong in
my analytic function in my sample and have to figure that out. :(

h, I figured it out. I had the parameters for the analytical function
right, I just gave it bad data to partition by when I worked up my
sample: because all the rows shared the same month it delivered weirdo
results. Thanks everyone for help so far, and this is a more
appropriate (and to Charles' point, more interesting) set of data.
Note that I'm hard-coding the date just for simplicity of the example
here.

--Assume this table for our dates

create table fiscal_weeks (
fiscal_year_week number PRIMARY KEY,
fiscal_year number,
fiscal_year_month number,
fiscal_year_week_LY number
);

--Assume this sales table:

create table sales_by_week (
fiscal_year_week number primary key,
sales number
);

--Here's some sample data:
insert into fiscal_weeks values (200601, 2006, 200601, 200501);
insert into fiscal_weeks values (200602, 2006, 200601, 200502);
insert into fiscal_weeks values (200603, 2006, 200601, 200503);
insert into fiscal_weeks values (200604, 2006, 200602, 200504);
insert into fiscal_weeks values (200501, 2005, 200501, 200401);
insert into fiscal_weeks values (200502, 2005, 200501, 200402);
insert into fiscal_weeks values (200503, 2005, 200501, 200403);
insert into fiscal_weeks values (200504, 2005, 200502, 200404);
insert into fiscal_weeks values (200401, 2004, 200401, 200301);
insert into fiscal_weeks values (200402, 2004, 200401, 200302);
insert into fiscal_weeks values (200403, 2004, 200401, 200303);
insert into fiscal_weeks values (200404, 2004, 200402, 200304);
insert into sales_by_week values (200601, 100);
insert into sales_by_week values (200602, 200);
insert into sales_by_week values (200603, 300);
insert into sales_by_week values (200501, 400);
insert into sales_by_week values (200502, 500);
insert into sales_by_week values (200503, 600);
insert into sales_by_week values (200401, 700);
insert into sales_by_week values (200402, 800);
insert into sales_by_week values (200403, 900);
insert into sales_by_week values (200604, 150);
insert into sales_by_week values (200504, 250);
insert into sales_by_week values (200404, 350);
commit;


--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_year_month
order by t2.fiscal_year_week
range between fiscal_year_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;

--The windowing functions are repeated for each of the cume periods I
need (QTD and YTD). But limiting just to MTD for the sake of this
example, this essentially gives me a set that is my weekly sales table
with cumulative, window-based sums, but I still need to get this year,
last year and two years ago in the same row. To do that I did the
following, which works, but is very costly and slow:

select ty.fiscal_year_week, ty.ty_sales_mtd, ly.ly_sales_mtd,
lly.lly_sales_mtd
from (
select t2.fiscal_year_week, t2.fiscal_year_week_ly, sales_mtd
as ty_sales_mtd
from sales_by_week_cume t1

erch wrote:

The reason that I included the other rows was to place some data in
another quarter and in another month to make certain that the
calculations were working correctly.

Analytic solution:
SELECT
FW.FISCAL_YEAR_WEEK,
FW.FISCAL_YEAR,
FW.FISCAL_MONTH,
TO_NUMBER(SUBSTR(TO_CHAR(SW.FISCAL_YEAR_WEEK),5)) FISCAL_WEEK_NUM,
SW.SALES,
SUM(SW.SALES) OVER (PARTITION BY FW.FISCAL_YEAR,FW.FISCAL_MONTH ORDER
BY FW.FISCAL_YEAR_WEEK ROWS BETWEEN 100 PRECEDING AND 0 FOLLOWING)
MTD_SALES,
SUM(SW.SALES) OVER (PARTITION BY
FW.FISCAL_YEAR,TRUNC(FW.FISCAL_MONTH/3) ORDER BY FW.FISCAL_YEAR_WEEK
ROWS BETWEEN 1000 PRECEDING AND 0 FOLLOWING) QTD_SALES,
SUM(SW.SALES) OVER (PARTITION BY FW.FISCAL_YEAR ORDER BY
FW.FISCAL_YEAR_WEEK ROWS BETWEEN 1000 PRECEDING AND 0 FOLLOWING)
YTD_SALES
FROM
FISCAL_WEEKS FW,
SALES_BY_WEEK SW
WHERE
FW.FISCAL_YEAR_WEEK=SW.FISCAL_YEAR_WEEK;

Output:
FISCAL_YEAR_WEEK FISCAL_YEAR FISCAL_MONTH FISCAL_WEEK_NUM SALES
MTD_SALES QTD_SALES YTD_SALES
200401 2004 1 1 700 700 700 700
200402 2004 1 2 800 1500 1500 1500
200403 2004 1 3 900 2400 2400 2400
200405 2004 2 5 1230 1230 3630 3630
200426 2004 7 26 333 333 333 3963
200501 2005 1 1 400 400 400 400
200502 2005 1 2 500 900 900 900
200503 2005 1 3 600 1500 1500 1500
200505 2005 2 5 1500 1500 3000 3000
200526 2005 7 26 444 444 444 3444
200601 2006 1 1 100 100 100 100
200602 2006 1 2 200 300 300 300
200603 2006 1 3 300 600 600 600
200605 2006 2 5 870 870 1470 1470
200626 2006 7 26 999 999 999 2469

Now to feed the above into the MAX DECODE syntax to combine the years:
SELECT
MAX(FISCAL_YEAR_WEEK) FISCAL_YEAR_WEEK,
MAX(FISCAL_YEAR) FISCAL_YEAR,
MAX(FISCAL_MONTH) FISCAL_MONTH,
FISCAL_WEEK_NUM,
MAX(DECODE(FISCAL_YEAR,TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')),SALES,0))
CUR_W_SALES,

MAX(DECODE(FISCAL_YEAR,TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE,-12),'YYYY')),SALES,0))
PRE_W_SALES,

MAX(DECODE(FISCAL_YEAR,TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE,-24),'YYYY')),SALES,0))
PRE2_W_SALES,

MAX(DECODE(FISCAL_YEAR,TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')),YTD_SALES,0))
CUR_YTD_SALES,

MAX(DECODE(FISCAL_YEAR,TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE,-12),'YYYY')),YTD_SALES,0))
PRE_YTD_SALES,

MAX(DECODE(FISCAL_YEAR,TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE,-24),'YYYY')),YTD_SALES,0))
PRE2_YTD_SALES,

MAX(DECODE(FISCAL_YEAR,TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')),MTD_SALES,0))
CUR_MTD_SALES,

MAX(DECODE(FISCAL_YEAR,TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE,-12),'YYYY')),MTD_SALES,0))
PRE_MTD_SALES,

MAX(DECODE(FISCAL_YEAR,TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE,-24),'YYYY')),MTD_SALES,0))
PRE2_MTD_SALES,

MAX(DECODE(FISCAL_YEAR,TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')),QTD_SALES,0))
CUR_QTD_SALES,

MAX(DECODE(FISCAL_YEAR,TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE,-12),'YYYY')),QTD_SALES,0))
PRE_QTD_SALES,

MAX(DECODE(FISCAL_YEAR,TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE,-24),'YYYY')),QTD_SALES,0))
PRE2_QTD_SALES
FROM
(SELECT
FW.FISCAL_YEAR_WEEK,
FW.FISCAL_YEAR,
FW.FISCAL_MONTH,
TO_NUMBER(SUBSTR(TO_CHAR(SW.FISCAL_YEAR_WEEK),5)) FISCAL_WEEK_NUM,
SW.SALES,
SUM(SW.SALES) OVER (PARTITION BY FW.FISCAL_YEAR,FW.FISCAL_MONTH ORDER
BY FW.FISCAL_YEAR_WEEK ROWS BETWEEN 100 PRECEDING AND 0 FOLLOWING)
MTD_SALES,
SUM(SW