Path: text.usenetserver.com!out03b.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!f1g2000cwa.googlegroups.com!not-for-mail From: "Charles Hooper" Newsgroups: comp.databases.oracle.server Subject: Re: Help on query Date: 12 Dec 2006 18:02:59 -0800 Organization: http://groups.google.com Lines: 235 Message-ID: <1165975379.595222.117480@f1g2000cwa.googlegroups.com> References: <1165962006.964669.26780@80g2000cwy.googlegroups.com> <1165972969.171039.303260@80g2000cwy.googlegroups.com> NNTP-Posting-Host: 63.158.169.80 Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" X-Trace: posting.google.com 1165975385 23475 127.0.0.1 (13 Dec 2006 02:03:05 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Wed, 13 Dec 2006 02:03:05 +0000 (UTC) In-Reply-To: <1165972969.171039.303260@80g2000cwy.googlegroups.com> User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322),gzip(gfe),gzip(gfe) Complaints-To: groups-abuse@google.com Injection-Info: f1g2000cwa.googlegroups.com; posting-host=63.158.169.80; posting-account=ytcoAwwAAADhCs0M3G1mFO5tqSfx4ge9 Xref: usenetserver.com comp.databases.oracle.server:419087 X-Received-Date: Tue, 12 Dec 2006 21:03:05 EST (text.usenetserver.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.