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: Help on query

Re: Help on query

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 12 Dec 2006 18:02:59 -0800
Message-ID: <1165975379.595222.117480@f1g2000cwa.googlegroups.com> 





Charles Hooper wrote:


> vishnu wrote:

> > Table Structure will be like this.

> > Hi,

> >

> >  I need to write Oracle query for this.Please help to solve this.

> >

> > Case ID, Major Type, Requested Date

> >

> > We need to find the count of cases which are over due, due today,

> > between 7-14 days etc as shown in the table.

> > Based on comparison between sysdate and requested date.

> >

> > Can this requirement fit into a query. ?

> >

> >

> > Case Summary

> > Major           Over Due        Due Today       Due Next 7-14 Days

> > Due Next 15-30 Days     Due Over 30 Days

> > Delivery                0       0       4       0       0

> > Return          0       0       0       0       0

> > Service         0       0       0       0       0

> > Relocation              0       0       0       0       0

> > Water Intrusion         0       0       0       0       0

> >

> >

> > Thanks,

> > Vishnu

>

> Looks like an interesting project.

>

> One method is to use the SIGN function, along with DECODE.  If you

> subtract two numbers and the result is negative, SIGN returns -1.  If

> the result is positive, SIGN returns 1.  If the result is 0, SIGN

> returns 0.  Date differences are treated as numbers.

>

> The set up:

> CREATE TABLE T1 (CASE_ID VARCHAR2(30),MAJOR_TYPE


> VARCHAR2(30),REQUESTED_DATE DATE);


>

> INSERT INTO T1 VALUES ('1','Delivery',TRUNC(SYSDATE-10));

> INSERT INTO T1 VALUES ('1','Delivery',TRUNC(SYSDATE-8));

> INSERT INTO T1 VALUES ('1','Delivery',TRUNC(SYSDATE));

> INSERT INTO T1 VALUES ('1','Delivery',TRUNC(SYSDATE+5));

> INSERT INTO T1 VALUES ('1','Delivery',TRUNC(SYSDATE+8));

> INSERT INTO T1 VALUES ('1','Delivery',TRUNC(SYSDATE+12));

> INSERT INTO T1 VALUES ('1','Delivery',TRUNC(SYSDATE+15));

> INSERT INTO T1 VALUES ('1','Delivery',TRUNC(SYSDATE+35));

> INSERT INTO T1 VALUES ('1','Return',TRUNC(SYSDATE+31));

>

> SELECT


>   CASE_ID,


>   MAJOR_TYPE,


>   REQUESTED_DATE,


>   REQUESTED_DATE-TRUNC(SYSDATE) DAYS_AWAY


> FROM


>   T1

> ORDER BY


>   CASE_ID,


>   MAJOR_TYPE,


>   REQUESTED_DATE;


>

> CASE_ID  MAJOR_TYPE  REQUESTED_DATE  DAYS_AWAY


> 1        Delivery    02-DEC-2006     -10

> 1        Delivery    04-DEC-2006      -8

> 1        Delivery    12-DEC-2006       0

> 1        Delivery    17-DEC-2006       5

> 1        Delivery    20-DEC-2006       8

> 1        Delivery    24-DEC-2006      12

> 1        Delivery    27-DEC-2006      15

> 1        Delivery    16-JAN-2007      35

> 1        Return      12-JAN-2007      31

>

> Now, start building formulas to determine if SIGN would return -1 for

> overdue or 0 for due today:

> CASE_ID  MAJOR_TYPE  REQUESTED_DATE  DAYS_AWAY  OVER_DUE  DUE_TODAY


> 1        Delivery    02-DEC-2006     -10        1         0

> 1        Delivery    04-DEC-2006      -8        1         0

> 1        Delivery    12-DEC-2006       0        0         1

> 1        Delivery    17-DEC-2006       5        0         0

> 1        Delivery    20-DEC-2006       8        0         0

> 1        Delivery    24-DEC-2006      12        0         0

> 1        Delivery    27-DEC-2006      15        0         0

> 1        Delivery    16-JAN-2007      35        0         0

> 1        Return      12-JAN-2007      31        0         0

>

> Those are the easy ones, now how to determine the 7 to 14 day range?

> If the date is greater than 6, but less than 15, it should be counted

> in this group.  This can be accomplished by a nested DECODE.  If the

> date comparision indicates that the date is greater than 6, it is

> compared again to see if it is also less than 15:

> SELECT


>   CASE_ID,


>   MAJOR_TYPE,


>   REQUESTED_DATE,


>   REQUESTED_DATE-TRUNC(SYSDATE) DAYS_AWAY,


>   DECODE(SIGN(REQUESTED_DATE-TRUNC(SYSDATE)),-1,1,0) OVER_DUE,


>   DECODE(SIGN(REQUESTED_DATE-TRUNC(SYSDATE)),0,1,0) DUE_TODAY,


>

> DECODE(SIGN(REQUESTED_DATE-(TRUNC(SYSDATE)+6)),1,DECODE(SIGN(REQUESTED_DATE-(TRUNC(SYSDATE)+15)),-1,1,0),0)


> DUE_7_14,


>

> DECODE(SIGN(REQUESTED_DATE-(TRUNC(SYSDATE)+14)),1,DECODE(SIGN(REQUESTED_DATE-(TRUNC(SYSDATE)+31)),-1,1,0),0)


> DUE_15_30,


>   DECODE(SIGN(REQUESTED_DATE-(TRUNC(SYSDATE)+30)),1,1,0) DUE_OVER_30


> FROM


>   T1

> ORDER BY


>   CASE_ID,


>   MAJOR_TYPE,


>   REQUESTED_DATE;


>

> CASE_ID  MAJOR_TYPE  REQUESTED_DATE  DAYS_AWAY  OVER_DUE  DUE_TODAY


> DUE_7_14  DUE_15_30  DUE_OVER_30


> 1        Delivery    02-DEC-2006     -10        1         0    0    0

>  0

> 1        Delivery    04-DEC-2006      -8        1         0    0    0

>  0

> 1        Delivery    12-DEC-2006       0        0         1    0    0

>  0

> 1        Delivery    17-DEC-2006       5        0         0    0    0

>  0

> 1        Delivery    20-DEC-2006       8        0         0    1    0

>  0

> 1        Delivery    24-DEC-2006      12        0         0    1    0

>  0

> 1        Delivery    27-DEC-2006      15        0         0    0    1

>  0

> 1        Delivery    16-JAN-2007      35        0         0    0    0

>  1

> 1        Return      12-JAN-2007      31        0         0    0    0

>  1

>

> Now it is just a simple matter of grouping and summing.

>

> Charles Hooper

> PC Support Specialist

> K&M Machine-Fabricating, Inc.




Another method, if the nested DECODE statements were too difficult to
decode.  This method is less efficient, so use with caution if the data
sets are large:


Next 7 to 14 days:


SELECT



  TRUNC(SYSDATE)+ROWNUM+6 CHECK_DATE



FROM



  DUAL



CONNECT BY LEVEL<=8;



     CHECK_DATE




19-DEC-2006
20-DEC-2006
21-DEC-2006
22-DEC-2006
23-DEC-2006
24-DEC-2006
25-DEC-2006
26-DEC-2006

Next 15 to 30 days:
     CHECK_DATE
====================
27-DEC-2006
28-DEC-2006
29-DEC-2006
30-DEC-2006
31-DEC-2006
01-JAN-2007
02-JAN-2007
03-JAN-2007
04-JAN-2007
05-JAN-2007
06-JAN-2007
07-JAN-2007
08-JAN-2007
09-JAN-2007




10-JAN-2007



11-JAN-2007


Now the tricky part, join those two list of dates (each in an inline
view) with the table from my previous post:
SELECT



  CASE_ID,



  MAJOR_TYPE,



  REQUESTED_DATE,



  REQUESTED_DATE-TRUNC(SYSDATE) DAYS_AWAY,

  DECODE(SIGN(REQUESTED_DATE-TRUNC(SYSDATE)),-1,1,0) OVER_DUE,

  DECODE(SIGN(REQUESTED_DATE-TRUNC(SYSDATE)),0,1,0) DUE_TODAY,

  NVL2(C7_14.CHECK_DATE,1,0) DUE_7_14,



  NVL2(C15_30.CHECK_DATE,1,0) DUE_15_30,

  DECODE(SIGN(REQUESTED_DATE-(TRUNC(SYSDATE)+30)),1,1,0) DUE_OVER_30

FROM



  T1,


  (SELECT



    TRUNC(SYSDATE)+ROWNUM+6 CHECK_DATE



  FROM



    DUAL



  CONNECT BY LEVEL<=8) C7_14,


  (SELECT



    TRUNC(SYSDATE)+ROWNUM+14 CHECK_DATE



  FROM



    DUAL



  CONNECT BY LEVEL<=16) C15_30


WHERE



  T1.REQUESTED_DATE=C7_14.CHECK_DATE(+)



  AND T1.REQUESTED_DATE=C15_30.CHECK_DATE(+)

ORDER BY



  CASE_ID,



  MAJOR_TYPE,



  REQUESTED_DATE;


Note that a left outer join was used between the REQUESTED_DATE date
and the dates in each of the two date ranges.  I then used NVL2 to
display a 1 if a date match was found (CHECK_DATE is not null), or a 0
if no date match was found (CHECK_DATE is null).



CASE_ID  MAJOR_TYPE  REQUESTED_DATE  DAYS_AWAY  OVER_DUE  DUE_TODAY

DUE_7_14  DUE_15_30  DUE_OVER_30


1   Delivery    02-DEC-2006     -10        1         0    0    0    0
1   Delivery    04-DEC-2006      -8        1         0    0    0    0
1   Delivery    12-DEC-2006       0        0         1    0    0    0
1   Delivery    17-DEC-2006       5        0         0    0    0    0
1   Delivery    20-DEC-2006       8        0         0    1    0    0
1   Delivery    24-DEC-2006      12        0         0    1    0    0
1   Delivery    27-DEC-2006      15        0         0    0    1    0
1   Delivery    16-JAN-2007      35        0         0    0    0    1
1   Return      12-JAN-2007      31        0         0    0    0    1





Other methods?



Charles Hooper


PC Support Specialist


K&M Machine-Fabricating, Inc.
Received on Tue Dec 12 2006 - 20:02:59 CST












Original text of this message













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