Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Is this possible in oracle 9.2 or is my logic way out on third base

Re: Is this possible in oracle 9.2 or is my logic way out on third base

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Thu, 31 May 2007 18:09:13 -0700
Message-ID: <1180660153.864476.261560@u30g2000hsc.googlegroups.com>


On May 31, 6:42 pm, MrHelpMe <clintto..._at_hotmail.com> wrote:
> Using oracle 9.2 and trying to construct a sql query that has the
> following. I will show the logic/code that I am trying to do and then
> explain.
> [code]
> WHERE
> trunc(field a) BETWEEN
> (trunc(sysdate,'DAY')-6) and (trunc(sysdate,'DAY')-2)
> AND
> trunc(field a) BETWEEN (trunc(sysdate + 90)) and
> (trunc(sysdate + 90,'DAY'))+ 5;
> [/code]
>
> I am trying to say within last week Monday to Friday date range, show
> me how many requests were greater then 90 days but only limit it to
> the end of the week of the beginning of that 90 day day.
>
> So last week May 21-25/07
> 90 days from may 21 = Lets just say August 21/07
> August 21/07 falls on a Tuesday so show everything from Tuesday to
> Friday
> There may be 300 entries that are greater then August 21/07 but I only
> want to capture anything till the end of the week of August 21/07.
> This will obviously change depending on what day the 90 days falls
> on. Am I making sense. So basically I am comparing everything that
> is happening in one week to a furture week so to speak.

I am not sure that I follow what you are trying to accomplish. Let's try a couple experiments to see if it gives you any ideas:

First, we will try to determine how to find the Monday and Friday of the current week:
SELECT
  NEXT_DAY(TRUNC(SYSDATE-7),'MONDAY'),
  TRUNC(SYSDATE),

  NEXT_DAY(TRUNC(SYSDATE),'FRIDAY'),
  NEXT_DAY(TRUNC(SYSDATE-1),'FRIDAY'),
  NEXT_DAY(TRUNC(SYSDATE+1),'FRIDAY')

FROM
  DUAL; NEXT_DAY( TRUNC(SYS NEXT_DAY( NEXT_DAY( NEXT_DAY( --------- --------- --------- --------- --------- 28-MAY-07 31-MAY-07 01-JUN-07 01-JUN-07 08-JUN-07 If today were Friday, rather than Thursday, the logical approach for finding Friday would actually return next Friday's date. The same applies to finding Monday's date. So, if we were trying to find this week Monday through Friday, you would use: NEXT_DAY(TRUNC(SYSDATE-7),'MONDAY') and
NEXT_DAY(TRUNC(SYSDATE-1),'FRIDAY') - to find last week, just subtract one day.

Moving forward, what if today plus 90 is a Saturday or Sunday: SELECT
  TRUNC(SYSDATE-4),
  TO_CHAR(SYSDATE-4,'D'),
  TO_CHAR(SYSDATE-4,'DAY')
FROM
  DUAL; TRUNC(SYS T TO_CHAR(S
--------- - ---------
27-MAY-07 1 SUNDAY TO_CHAR(SYSDATE-4,'D') returns 1 if four days ago is a Sunday and 7 if four days ago is a Saturday.

So, if TO_CHAR(SYSDATE+90,'D') returns '1' or '7', we want the following Monday, otherwise just return TRUNC(SYSDATE+90): SELECT
  DECODE(TO_CHAR(SYSDATE+90,'D'),'1',TRUNC((SYSDATE+90)+1), 7,TRUNC((SYSDATE+90)+2),TRUNC(SYSDATE+90)),   TRUNC(SYSDATE+90),
  NEXT_DAY(TRUNC((SYSDATE+90)-1),'FRIDAY') FROM
  DUAL; DECODE(TO TRUNC(SYS NEXT_DAY(
--------- --------- ---------
29-AUG-07 29-AUG-07 31-AUG-07 This implies:
WHERE
  TRUNC(MY_COLUMN) BETWEEN DECODE(TO_CHAR(SYSDATE

+90,'D'),'1',TRUNC((SYSDATE+90)+1),7,TRUNC((SYSDATE
+90)+2),TRUNC(SYSDATE+90)) AND NEXT_DAY(TRUNC((SYSDATE
+90)-1),'FRIDAY')

I don't like using TRUNC(COLUMN) as you did in your WHERE clause, so I might write it this way:
WHERE
  MY_COLUMN >= DECODE(TO_CHAR(SYSDATE+90,'D'),'1',TRUNC((SYSDATE +90)+1),7,TRUNC((SYSDATE+90)+2),TRUNC(SYSDATE+90))   AND MY_COLUMN < NEXT_DAY(TRUNC((SYSDATE+90)-1),'FRIDAY')+1

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Thu May 31 2007 - 20:09:13 CDT

Original text of this message

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