by Charles Ho » Sat, 11 Nov 2006 03:32:58
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