| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Help on query
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.
Received on Tue Dec 12 2006 - 19:22:49 CST
 |
 |