Path: text.usenetserver.com!out04a.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!80g2000cwy.googlegroups.com!not-for-mail From: "Charles Hooper" Newsgroups: comp.databases.oracle.server Subject: Re: Help on query Date: 12 Dec 2006 17:22:49 -0800 Organization: http://groups.google.com Lines: 138 Message-ID: <1165972969.171039.303260@80g2000cwy.googlegroups.com> References: <1165962006.964669.26780@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 1165972975 816 127.0.0.1 (13 Dec 2006 01:22:55 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Wed, 13 Dec 2006 01:22:55 +0000 (UTC) In-Reply-To: <1165962006.964669.26780@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: 80g2000cwy.googlegroups.com; posting-host=63.158.169.80; posting-account=ytcoAwwAAADhCs0M3G1mFO5tqSfx4ge9 Xref: usenetserver.com comp.databases.oracle.server:419084 X-Received-Date: Tue, 12 Dec 2006 20:22:55 EST (text.usenetserver.com) 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.