Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Is this possible in oracle 9.2 or is my logic way out on third base
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')
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