Re: Oracle query assistence

On Nov 14, 5:39 am, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Nov 14, 7:14 am, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
>
> > On Nov 13, 7:46 pm, trp..._at_gmail.com wrote:
> > > I have a table defined as follows:
>
> > > CREATE TABLE products
> > > (
> > > report_date DATE not null,
> > > product_name varchar2(128) not null,
> > > num_cases number(4) not null,
> > > CONSTRAINT peoplesoftProduct_pk PRIMARY KEY (report_date,
> > > product_name)
> > > );
>
> > > Here is a query I am running that produces the data I am after:
>
> > > SELECT PRODUCT_NAME, NUM_CASES,
> > > to_char(REPORT_DATE,'Mon YYYY') AS "Report Date"
> > > FROM PRODUCTS
> > > WHERE PRODUCT_NAME IN ('Product1', 'Product2')
> > > AND report_date < to_date ('2007-09-30','YYYY-MM-DD')
> > > AND report_date > to_date ('2007-09-30','YYYY-MM-DD') - 365
> > > ORDER BY PRODUCT_NAME ASC, REPORT_DATE ASC
>
> > > Here is the output:
>
> > > PRODUCT_NAME NUM_CASES Report Date
> > > Product1 429 Nov 2006
> > > Product1 238 Dec 2006
> > > Product1 304 Jan 2007
> > > Product1 284 Feb 2007
> > > Product1 271 Mar 2007
> > > Product1 352 Apr 2007
> > > Product1 422 May 2007
> > > Product1 242 Jun 2007
> > > Product1 220 Jul 2007
> > > Product1 211 Aug 2007
> > > Product1 247 Sep 2007
> > > Product2 176 Nov 2006
> > > Product2 152 Dec 2006
> > > Product2 157 Jan 2007
> > > Product2 126 Feb 2007
> > > Product2 139 Mar 2007
> > > Product2 156 Apr 2007
> > > Product2 206 May 2007
> > > Product2 206 Jun 2007
> > > Product2 262 Jul 2007
> > > Product2 213 Aug 2007
> > > Product2 187 Sep 2007
>
> > > The way I actually want the data to display from a query is as
> > > follows:
>
> > > Nov 2006 Dec 2006 Jan
> > > 2007 ...
> > > Product1 429 238 304 ...
> > > Product2 176 152 157 ...
>
> > > Now I know I could use the above query and write a script to process
> > > the data to make it output as I wish, but I really want to do this in
> > > a query. Is this possible and how would I adapt the above query to do
> > > so?
>
> > > Thanks!
>
> > Let's take a look to see if there are ways to create cross-tab style
> > reports.
>
> > First, an experiment to see if we can have Oracle count the months,
> > starting with a particular month start date:
> > SELECT
> > LEVEL POSITION,
> > ADD_MONTHS(TO_DATE('2007-10-01','YYYY-MM-DD') - 365, LEVEL-1)
> > MONTH_START
> > FROM
> > DUAL
> > CONNECT BY
> > LEVEL<=12;
>
> > POSITION MONTH_STA
> > ---------- ---------
> > 1 01-OCT-06
> > 2 01-NOV-06
> > 3 01-DEC-06
> > 4 01-JAN-07
> > 5 01-FEB-07
> > 6 01-MAR-07
> > 7 01-APR-07
> > 8 01-MAY-07
> > 9 01-JUN-07
> > 10 01-JUL-07
> > 11 01-AUG-07
> > 12 01-SEP-07
>
> > Now that we see that is possible, we should be able to use a
> > combination of MAX, DECODE, and GROUP BY to develop a solution.
> > First, the set up:
> > CREATE TABLE T1 (
> > REPORT_DATE DATE,
> > PRODUCT_NAME VARCHAR2(128),
> > NUM_CASES NUMBER(4));
>
> > INSERT INTO T1 (PRODUCT_NAME, NUM_CASES, REPORT_DATE) VALUES
> > ('Product1',429,'01-NOV-2006');
> > INSERT INTO T1 (PRODUCT_NAME, NUM_CASES, REPORT_DATE) VALUES
> > ('Product1',238,'01-DEC-2006');
> > INSERT INTO T1 (PRODUCT_NAME, NUM_CASES, REPORT_DATE) VALUES
> > ('Product1',304,'01-JAN-2007');
> > INSERT INTO T1 (PRODUCT_NAME, NUM_CASES, REPORT_DATE) VALUES
> > ('Product1',284,'01-FEB-2007');
> > INSERT INTO T1 (PRODUCT_NAME, NUM_CASES, REPORT_DATE) VALUES
> > ('Product1',271,'01-MAR-2007');
> > INSERT INTO T1 (PRODUCT_NAME, NUM_CASES, REPORT_DATE) VALUES
> > ('Product1',352,'01-APR-2007');
> > INSERT INTO T1 (PRODUCT_NAME, NUM_CASES, REPORT_DATE) VALUES
> > ('Product1',422,'01-MAY-2007');
> > INSERT INTO T1 (PRODUCT_NAME, NUM_CASES, REPORT_DATE) VALUES
> > ('Product1',242,'01-JUN-2007');
> > INSERT INTO T1 (PRODUCT_NAME, NUM_CASES, REPORT_DATE) VALUES
> > ('Product1',220,'01-JUL-2007');
> > INSERT INTO T1 (PRODUCT_NAME, NUM_CASES, REPORT_DATE) VALUES
> > ('Product1',211,'01-AUG-2007');
> > INSERT INTO T1 (PRODUCT_NAME, NUM_CASES, REPORT_DATE) VALUES
> > ('Product1',247,'01-SEP-2007');
> > INSERT INTO T1 (PRODUCT_NAME, NUM_CASES, REPORT_DATE) VALUES
> > ('Product2',176,'01-NOV-2006');
> > INSERT INTO T1 (PRODUCT_NAME, NUM_CASES, REPORT_DATE) VALUES
> > ('Product2',152,'01-DEC-2006');
> > INSERT INTO T1 (PRODUCT_NAME, NUM_CASES, REPORT_DATE) VALUES
> > ('Product2',157,'01-JAN-2007');
> > INSERT INTO T1 (PRODUCT_NAME, NUM_CASES, REPORT_DATE) VALUES
> > ('Product2',126,'01-FEB-2007');
> > INSERT INTO T1 (PRODUCT_NAME, NUM_CASES, REPORT_DATE) VALUES
> > ('Product2',139,'01-MAR-2007');
> > INSERT INTO T1 (PRODUCT_NAME, NUM_CASES, REPORT_DATE) VALUES
> > ('Product2',156,'01-APR-2007');
> > INSERT INTO T1 (PRODUCT_NAME, NUM_CASES, REPORT_DATE) VALUES
> > ('Product2',206,'01-MAY-2007');
> > INSERT INTO T1 (PRODUCT_NAME, NUM_CASES, REPORT_DATE) VALUES
> > ('Product2',206,'01-JUN-2007');
> > INSERT INTO T1 (PRODUCT_NAME, NUM_CASES, REPORT_DATE) VALUES
> > ('Product2',262,'01-JUL-2007');
> > INSERT INTO T1 (PRODUCT_NAME, NUM_CASES, REPORT_DATE) VALUES
> > ('Product2',213,'01-AUG-2007');
> > INSERT INTO T1 (PRODUCT_NAME, NUM_CASES, REPORT_DATE) VALUES
> > ('Product2',187,'01-SEP-2007');
>
> > Now, let's take the SQL statement that we previously used above and
> > join it to a simple select statement with our T1 table - we will slide
> > each into an inline view:
> > SELECT
> > T.PRODUCT_NAME,
> > T.REPORT_DATE,
> > T.NUM_CASES,
> > C.POSITION
> > FROM
> > (SELECT
> > PRODUCT_NAME,
> > REPORT_DATE,
> > NUM_CASES
> > FROM
> > T1) T,
> > (SELECT
> > LEVEL POSITION,
> > ADD_MONTHS(TO_DATE('2007-10-01','YYYY-MM-DD') - 365, LEVEL-1)
> > MONTH_START
> > FROM
> > DUAL
> > CONNECT BY
> > LEVEL<=12) C
> > WHERE
> > T.REPORT_DATE=C.MONTH_START;
>
> > PRODUCT_ REPORT_DA NUM_CASES POSITION
> > -------- --------- ---------- ----------
> > Product1 01-NOV-06 429 2
> > Product1 01-DEC-06 238 3
> > Product1 01-JAN-07 304 4
> > Product1 01-FEB-07 284 5
> > Product1 01-MAR-07 271 6
> > Product1 01-APR-07 352 7
> > Product1 01-MAY-07 422 8
> > Product1 01-JUN-07 242 9
> > Product1 01-JUL-07 220 10
> > Product1 01-AUG-07 211 11
> > Product1 01-SEP-07 247 12
> > Product2 01-NOV-06 176 2
> > Product2 01-DEC-06 152 3
> > Product2 01-JAN-07 157 4
> > Product2 01-FEB-07 126 5
> > Product2 01-MAR-07 139 6
> > Product2 01-APR-07 156 7
> > Product2 01-MAY-07 206 8
> > Product2 01-JUN-07 206 9
> > Product2 01-JUL-07 262 10
> > Product2 01-AUG-07 213 11
> > Product2 01-SEP-07 187 12
>
> > You will likely note that there is now a counter next to each row from
> > the T1 table - we will use this with DECODE, MAX and GROUP BY to
> > collapse all rows for a single PRODUCT_NAME into a single row, and
> > place each of the NUM_CASES into a separate column based on the value
> > of POSITION:
> > SELECT
> > T.PRODUCT_NAME,
> > MAX(DECODE(C.POSITION,1,T.NUM_CASES,NULL)) P1,
> > MAX(DECODE(C.POSITION,2,T.NUM_CASES,NULL)) P2,
> > MAX(DECODE(C.POSITION,3,T.NUM_CASES,NULL)) P3,
> > MAX(DECODE(C.POSITION,4,T.NUM_CASES,NULL)) P4,
> > MAX(DECODE(C.POSITION,5,T.NUM_CASES,NULL)) P5,
> > MAX(DECODE(C.POSITION,6,T.NUM_CASES,NULL)) P6,
> > MAX(DECODE(C.POSITION,7,T.NUM_CASES,NULL)) P7,
> > MAX(DECODE(C.POSITION,8,T.NUM_CASES,NULL)) P8,
> > MAX(DECODE(C.POSITION,9,T.NUM_CASES,NULL)) P9,
> > MAX(DECODE(C.POSITION,10,T.NUM_CASES,NULL)) P10,
> > MAX(DECODE(C.POSITION,11,T.NUM_CASES,NULL)) P11,
> > MAX(DECODE(C.POSITION,12,T.NUM_CASES,NULL)) P12
> > FROM
> > (SELECT
> > PRODUCT_NAME,
> > REPORT_DATE,
> > NUM_CASES
> > FROM
> > T1) T,
> > (SELECT
> > LEVEL POSITION,
> > ADD_MONTHS(TO_DATE('2007-10-01','YYYY-MM-DD') - 365, LEVEL-1)
> > MONTH_START
> > FROM
> > DUAL
> > CONNECT BY
> > LEVEL<=12) C
> > WHERE
> > T.REPORT_DATE=C.MONTH_START
> > GROUP BY
> > T.PRODUCT_NAME;
>
> > (following output is shortened by width):
> > PRODUCT_ P1 P2 P3 P11 P12
> > -------- ---------- ---------- ---------- ---------- ----------
> > Product1 429 238 211 247
> > Product2 176 152 213 187
>
> > There are of course other ways to accomplish the above - this is just
> > one method. If you search this group (or the other
> > comp.databases.oracle.x groups) using Google, you will likely find
> > other examples, including this one:
> > http://groups.google.com/group/comp.databases.oracle.misc/browse_thre...
>
> > Charles Hooper
> > IT Manager/Oracle DBA
> > K&M Machine-Fabricating, Inc
>
> If you are certain that there will be no missing months, you can take
> a shortcut using analytical functions:
> SELECT
> T.PRODUCT_NAME,
> T.NUM_CASES,
> ROW_NUMBER() OVER (PARTITION BY T.PRODUCT_NAME ORDER BY
> T.REPORT_DATE) POSITION
> FROM
> T1 T;
>
> SUBSTR(T NUM_CASES POSITION
> -------- ---------- ----------
> Product1 429 1
> Product1 238 2
> Product1 304 3
> Product1 284 4
> Product1 271 5
> Product1 352 6
> Product1 422 7
> Product1 242 8
> Product1 220 9
> Product1 211 10
> Product1 247 11
> Product2 176 1
> Product2 152 2
> Product2 157 3
> Product2 126 4
> Product2 139 5
> Product2 156 6
> Product2 206 7
> Product2 206 8
> Product2 262 9
> Product2 213 10
> Product2 187 11
>
> And then jump to the partial solution as before:
> SELECT
> T.PRODUCT_NAME,
> MAX(DECODE(C.POSITION,1,T.NUM_CASES,NULL)) P1,
> MAX(DECODE(C.POSITION,2,T.NUM_CASES,NULL)) P2,
> MAX(DECODE(C.POSITION,3,T.NUM_CASES,NULL)) P3,
> MAX(DECODE(C.POSITION,4,T.NUM_CASES,NULL)) P4,
> MAX(DECODE(C.POSITION,5,T.NUM_CASES,NULL)) P5,
> MAX(DECODE(C.POSITION,6,T.NUM_CASES,NULL)) P6,
> MAX(DECODE(C.POSITION,7,T.NUM_CASES,NULL)) P7,
> MAX(DECODE(C.POSITION,8,T.NUM_CASES,NULL)) P8,
> MAX(DECODE(C.POSITION,9,T.NUM_CASES,NULL)) P9,
> MAX(DECODE(C.POSITION,10,T.NUM_CASES,NULL)) P10,
> MAX(DECODE(C.POSITION,11,T.NUM_CASES,NULL)) P11,
> MAX(DECODE(C.POSITION,12,T.NUM_CASES,NULL)) P12
> FROM
> (SELECT
> T.PRODUCT_NAME,
> T.NUM_CASES,
> ROW_NUMBER() OVER (PARTITION BY T.PRODUCT_NAME ORDER BY
> T.REPORT_DATE) POSITION
> FROM
> T1 T) T
> GROUP BY
> T.PRODUCT_NAME;
>
> SUBSTR(T P1 P2 P3 P11 P12
> -------- ---------- ---------- ---------- ---------- ----------
> Product1 429 238 304 247
> Product2 176 152 157 187
>
> Note that P1 now has a value and P12 does not.
>
> Kind of make you wonder if this confusing SQL statement will also
> work:
> SELECT
> T.PRODUCT_NAME,
> MAX(DECODE(T.REPORT_DATE,ADD_MONTHS(TO_DATE('2007-10-01','YYYY-MM-
> DD') - 365, 0),T.NUM_CASES,NULL)) P1,
> MAX(DECODE(T.REPORT_DATE,ADD_MONTHS(TO_DATE('2007-10-01','YYYY-MM-
> DD') - 365, 1),T.NUM_CASES,NULL)) P2,
> MAX(DECODE(T.REPORT_DATE,ADD_MONTHS(TO_DATE('2007-10-01','YYYY-MM-
> DD') - 365, 2),T.NUM_CASES,NULL)) P3,
> MAX(DECODE(T.REPORT_DATE,ADD_MONTHS(TO_DATE('2007-10-01','YYYY-MM-
> DD') - 365, 10),T.NUM_CASES,NULL)) P11,
> MAX(DECODE(T.REPORT_DATE,ADD_MONTHS(TO_DATE('2007-10-01','YYYY-MM-
> DD') - 365, 11),T.NUM_CASES,NULL)) P12
> FROM
> T1 T
> GROUP BY
> T.PRODUCT_NAME;
>
> PRODUCT_ P1 P2 P3 P11 P12
> -------- ---------- ---------- ---------- ---------- ----------
> Product1 429 238 211 247
> Product2 176 152 213 187
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.

Thanks for all your help, this is real close, only thing I need differently is the columns to display the month/year, so instead of P1,P2,P3,etc... it needs to be something like Nov 2006, Dec 2006, Jan 2007, etc...

But the months cannot be a hard coded value, they need to be dynamic based on the associated REPORT_DATE.

Any ideas?? Received on Mon Nov 26 2007 - 17:47:02 CST