Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Advanced Oracle SQL Book ? (was: Reading column into a string variable)

Re: Advanced Oracle SQL Book ? (was: Reading column into a string variable)

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 14 Nov 2006 09:13:04 -0800
Message-ID: <1163524383.976848.218770@i42g2000cwa.googlegroups.com> 





zomer wrote:

> Michel and Charles.... thank you for very helpful posts. If you have
> have more examples of advanced sql functions please post them as they
> will help.





Obviously, these SQL statements will not work as is with your system,
but hopefully they are documented well enough that the possibilities
will be obvious.






NVL2:



Using NVL2 to do one thing if a column is NULL and something else if
the column is not null, how directly productive are employees:
SELECT



  EMPLOYEE_ID,



  SUM(NVL2(RESOURCE_ID,HOURS_WORKED,-HOURS_WORKED)) HOURS_PRODUCTIVE

FROM



  LABOR_TICKET



WHERE



  SHIFT_DATE>=TRUNC(SYSDATE-7)



GROUP BY



  EMPLOYEE_ID



ORDER BY



  EMPLOYEE_ID;







COALESCE:



Find the First Non Null Value in a List of columns:
SELECT



  POL.PART_ID,


COALESCE(PLD.DESIRED_RECV_DATE,POL.DESIRED_RECV_DATE,PO.DESIRED_RECV_DATE)

DESIRED_RECV_DATE



FROM



  PURCHASE_ORDER PO,



  PURC_ORDER_LINE POL,



  PURC_LINE_DEL PLD



WHERE



  PO.STATUS='R'



  AND PO.ID=POL.PURC_ORDER_ID



  AND POL.PURC_ORDER_ID=PLD.PURC_ORDER_ID(+)

  AND POL.LINE_NO=PLD.PURC_ORDER_LINE_NO(+);







GREATEST - Find the largest value in a list of columns (LEAST works the
same way):


Find the highest quantity break for each vendor quote:
SELECT



  VENDOR_ID,



  VENDOR_PART_ID,


GREATEST(NVL(QTY_BREAK_1,0),NVL(QTY_BREAK_2,0),NVL(QTY_BREAK_3,0),NVL(QTY_BREAK_4,0),NVL(QTY_BREAK_5,0),NVL(QTY_BREAK_6,0),NVL(QTY_BREAK_7,0),NVL(QTY_BREAK_8,0),NVL(QTY_BREAK_9,0),NVL(QTY_BREAK_10,0))

QB


FROM



  VENDOR_QUOTE;







CONCAT:



Concatenate 2 (varchar) character columns, similar to ||
SELECT



  ID,


  DESCRIPTION,



  CONCAT(ID,DESCRIPTION),



  ID||DESCRIPTION



FROM



  PART;







DENSE_RANK WITHIN GROUP, COUNT DISTINCT



Find the ranking of a ficticious item within the group having a
UNIT_MATERIAL_COST cost of 500, when sorted most expensive to least
expensive


SELECT



  PRODUCT_CODE,



  DENSE_RANK(500) WITHIN GROUP (ORDER BY UNIT_MATERIAL_COST DESC NULLS

LAST) RELATIVE_RANK_500,



  COUNT(DISTINCT UNIT_MATERIAL_COST) UNIQUE_VALUES_IN_GRP

FROM



  PART



GROUP BY



  PRODUCT_CODE;







RANK(), AVG(), MIN(), MAX(), COUNT OVER(PARTITION):

Analyze the UNIT_MATERIAL_COST column in the PART table.  For each
part, find the relative cost (high to low) ranking, average cost,
smallest cost, highest cost, and the total number in each group, when
the parts are grouped individually by product code, commodity code, and
also preferred vendor (all parts without a preferred vendor are grouped
together):


SELECT



  ID,


  DESCRIPTION,



  UNIT_MATERIAL_COST,



  RANK() OVER (PARTITION BY PRODUCT_CODE ORDER BY UNIT_MATERIAL_COST

DESC NULLS LAST) RANK_PC_COST,



  AVG(UNIT_MATERIAL_COST) OVER (PARTITION BY PRODUCT_CODE ORDER BY

UNIT_MATERIAL_COST) AVG_PC_COST,



  MIN(UNIT_MATERIAL_COST) OVER (PARTITION BY PRODUCT_CODE ORDER BY

UNIT_MATERIAL_COST) MIN_PC_COST,



  MAX(UNIT_MATERIAL_COST) OVER (PARTITION BY PRODUCT_CODE ORDER BY

UNIT_MATERIAL_COST) MAX_PC_COST,



  COUNT(UNIT_MATERIAL_COST) OVER (PARTITION BY PRODUCT_CODE ORDER BY

UNIT_MATERIAL_COST) COUNT_PC,



  RANK() OVER (PARTITION BY COMMODITY_CODE ORDER BY UNIT_MATERIAL_COST

DESC NULLS LAST) RANK_CC_COST,



  AVG(UNIT_MATERIAL_COST) OVER (PARTITION BY COMMODITY_CODE ORDER BY

UNIT_MATERIAL_COST) AVG_CC_COST,



  MIN(UNIT_MATERIAL_COST) OVER (PARTITION BY COMMODITY_CODE ORDER BY

UNIT_MATERIAL_COST) MIN_CC_COST,



  MAX(UNIT_MATERIAL_COST) OVER (PARTITION BY COMMODITY_CODE ORDER BY

UNIT_MATERIAL_COST) MAX_CC_COST,



  COUNT(UNIT_MATERIAL_COST) OVER (PARTITION BY COMMODITY_CODE ORDER BY

UNIT_MATERIAL_COST) COUNT_CC,



  RANK() OVER (PARTITION BY NVL(PREF_VENDOR_ID,'IN_HOUSE_FAB') ORDER BY

UNIT_MATERIAL_COST DESC NULLS LAST) RANK_VENDOR_COST,

  AVG(UNIT_MATERIAL_COST) OVER (PARTITION BY

NVL(PREF_VENDOR_ID,'IN_HOUSE_FAB') ORDER BY UNIT_MATERIAL_COST)

AVG_VENDOR_COST,



  MIN(UNIT_MATERIAL_COST) OVER (PARTITION BY

NVL(PREF_VENDOR_ID,'IN_HOUSE_FAB') ORDER BY UNIT_MATERIAL_COST)

MIN_VENDOR_COST,



  MAX(UNIT_MATERIAL_COST) OVER (PARTITION BY

NVL(PREF_VENDOR_ID,'IN_HOUSE_FAB') ORDER BY UNIT_MATERIAL_COST)

MAX_VENDOR_COST,



  COUNT(UNIT_MATERIAL_COST) OVER (PARTITION BY PREF_VENDOR_ID ORDER BY

UNIT_MATERIAL_COST) COUNT_VENDOR



FROM



  PART



ORDER BY



  ID;






LAG, LEAD OVER(PARTITION), NEXT_DAY, Inline view
Show the sum of the hours worked for each employee by shift date, along
with the previous five days and the next 5 days, and the next Monday
after the shift date:


SELECT



  EMPLOYEE_ID,



  SHIFT_DATE,



  NEXT_DAY(SHIFT_DATE,'MONDAY') PAYROLL_PREPARE_DATE,

  LAG(HOURS_WORKED,5,0) OVER (PARTITION BY EMPLOYEE_ID ORDER BY

SHIFT_DATE) PREV5_HOURS,



  LAG(HOURS_WORKED,4,0) OVER (PARTITION BY EMPLOYEE_ID ORDER BY

SHIFT_DATE) PREV4_HOURS,



  LAG(HOURS_WORKED,3,0) OVER (PARTITION BY EMPLOYEE_ID ORDER BY

SHIFT_DATE) PREV3_HOURS,



  LAG(HOURS_WORKED,2,0) OVER (PARTITION BY EMPLOYEE_ID ORDER BY

SHIFT_DATE) PREV2_HOURS,



  LAG(HOURS_WORKED,1,0) OVER (PARTITION BY EMPLOYEE_ID ORDER BY

SHIFT_DATE) PREV_HOURS,



  HOURS_WORKED,



  LEAD(HOURS_WORKED,1,0) OVER (PARTITION BY EMPLOYEE_ID ORDER BY

SHIFT_DATE) NEXT_HOURS,



  LEAD(HOURS_WORKED,2,0) OVER (PARTITION BY EMPLOYEE_ID ORDER BY

SHIFT_DATE) NEXT2_HOURS,



  LEAD(HOURS_WORKED,3,0) OVER (PARTITION BY EMPLOYEE_ID ORDER BY

SHIFT_DATE) NEXT3_HOURS,



  LEAD(HOURS_WORKED,4,0) OVER (PARTITION BY EMPLOYEE_ID ORDER BY

SHIFT_DATE) NEXT4_HOURS,



  LEAD(HOURS_WORKED,5,0) OVER (PARTITION BY EMPLOYEE_ID ORDER BY

SHIFT_DATE) NEXT5_HOURS



FROM



  (SELECT



    EMPLOYEE_ID,



    SHIFT_DATE,



    SUM(HOURS_WORKED) HOURS_WORKED



  FROM



    LABOR_TICKET



  WHERE



    SHIFT_DATE>=TRUNC(SYSDATE-14)



  GROUP BY



    EMPLOYEE_ID,



    SHIFT_DATE



  ORDER BY



    EMPLOYEE_ID,



    SHIFT_DATE);







PERCENT_RANK() OVER (PARTITION):



Retrieve a value between 0 and 1 that indicates the relative position
in a ranking


SELECT



  ID,


  DESCRIPTION,



  PERCENT_RANK() OVER (PARTITION BY PRODUCT_CODE ORDER BY

UNIT_MATERIAL_COST) RANKING



FROM



  PART



ORDER BY



  ID;





PERCENTILE_CONT, PERCENTILE_DISC WITHIN GROUP

Grouped by product code, find the UNIT_MATERIAL_COST that would be
ranked 25%, 50%, and 75% of the way through the product code when
sorted by UNIT_MATERIAL_COST.  PERCENTILE_CONT averages the two values
if both would match the location, while PERCENTILE_DISC picks the first
at the location


SELECT



  PRODUCT_CODE,


  PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY UNIT_MATERIAL_COST)
MEDIAN_PRICE_AVG_25,
  PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY UNIT_MATERIAL_COST)
MEDIAN_PRICE_25,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY UNIT_MATERIAL_COST)
MEDIAN_PRICE_AVG_50,
  PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY UNIT_MATERIAL_COST)
MEDIAN_PRICE_50,
  PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY UNIT_MATERIAL_COST)
MEDIAN_PRICE_AVG_75,
  PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY UNIT_MATERIAL_COST)
MEDIAN_PRICE_75




FROM



  PART



GROUP BY



  PRODUCT_CODE;







RANK() WITHIN GROUP:



Find the five highest unit material costs when grouped by product code:
SELECT



  PRODUCT_CODE,



  RANK(1) WITHIN GROUP (ORDER BY UNIT_MATERIAL_COST DESC NULLS LAST)

UNIT_PRICE,



  RANK(2) WITHIN GROUP (ORDER BY UNIT_MATERIAL_COST DESC NULLS LAST)

UNIT_PRICE,



  RANK(3) WITHIN GROUP (ORDER BY UNIT_MATERIAL_COST DESC NULLS LAST)

UNIT_PRICE,



  RANK(4) WITHIN GROUP (ORDER BY UNIT_MATERIAL_COST DESC NULLS LAST)

UNIT_PRICE,



  RANK(5) WITHIN GROUP (ORDER BY UNIT_MATERIAL_COST DESC NULLS LAST)

UNIT_PRICE



FROM



  PART



GROUP BY



  PRODUCT_CODE



ORDER BY



  PRODUCT_CODE;







RATIO_TO_REPORT:



Compare the number of hours worked per labor ticket with the total
number of hours worked in the specified date range:
SELECT



  EMPLOYEE_ID,



  TRANSACTION_ID,



  RESOURCE_ID,



  INDIRECT_ID,



  HOURS_WORKED,



  RATIO_TO_REPORT (HOURS_WORKED) OVER () PERCENT_OF_TOTAL_HRS

FROM



  LABOR_TICKET



WHERE



  SHIFT_DATE=TRUNC(SYSDATE-1)



ORDER BY



  EMPLOYEE_ID,



  CLOCK_IN;







RATIO_TO_REPORT, Inline view:


Ratio of Each Indirect ID to the total number of hours worked in the
specified time period:


SELECT



  INDIRECT_ID,



  TOTAL_HOURS,



  RATIO_TO_REPORT (TOTAL_HOURS) OVER () PERCENT_OF_TOTAL_HRS

FROM



  (SELECT



    NVL(INDIRECT_ID,'DIRECT LABOR') INDIRECT_ID,

    SUM(HOURS_WORKED) TOTAL_HOURS



  FROM



    LABOR_TICKET



  WHERE



    SHIFT_DATE=TRUNC(SYSDATE-1)



  GROUP BY



    NVL(INDIRECT_ID,'DIRECT LABOR'));







SUM OVER (PARTITION BETWEEN x PRECEDING AND y FOLLOWING), MAX(DECODE)



Tables:


Lists the fiscal years:


CREATE TABLE FISCAL_WEEKS (


  FISCAL_YEAR_WEEK NUMBER PRIMARY KEY,
  FISCAL_YEAR NUMBER,
  FISCAL_MONTH NUMBER,
  FISCAL_YEAR_WEEK_LY NUMBER);





Lists the aggregated sales by week:


CREATE TABLE SALES_BY_WEEK (



  FISCAL_YEAR_WEEK NUMBER PRIMARY KEY,



  SALES NUMBER);


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


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



COMMIT;



The question, how to see the year to date, quarter to date, month to
date, and weekly totals for the current period and the same period for
the previous two years, all on a single row:



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;




The problem is that the results are not all on a since row per period:



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




We can fix that by sliding the above into an inline view and use MAX
and DECODE to place the different years values on the same row.  The
resulting SQL statement looks like this:



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

GROUP BY



  FISCAL_WEEK_NUM



ORDER BY



  1;




The above is logically equivalent to inefficient code, which I coded
without 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;






Hopefully, some of the above helps solve an odd analysis technique for
someone.
 


Charles Hooper


PC Support Specialist


K&M Machine-Fabricating, Inc.
Received on Tue Nov 14 2006 - 11:13:04 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US