Elegant
pawan, January 23, 2003 - 12:01 pm UTC
Calculate fourth Thursday of a Month
Rich, January 23, 2003 - 12:54 pm UTC
Thanks for the answer. I went about it a little differently, using analytic functions and had a question.
I got the same answer you did writing it this way:
select DT, DAY, SEQ
from (select dt, rj, DAY,
row_number() over (partition by to_char(dt,'mm') order by dt) "SEQ"
from (select trunc(sysdate,'year')+rownum-1 dt,
to_char(trunc(sysdate,'year')+rownum-1, 'd') rj,
to_char(trunc(sysdate,'year')+rownum-1, 'Day') "DAY"
from all_objects
where rownum <= 365) rs
where rj = '5')
where SEQ = '4'
DT DAY SEQ
--------- --------- ----------
23-JAN-03 Thursday 4
27-FEB-03 Thursday 4
27-MAR-03 Thursday 4
24-APR-03 Thursday 4
22-MAY-03 Thursday 4
26-JUN-03 Thursday 4
24-JUL-03 Thursday 4
28-AUG-03 Thursday 4
25-SEP-03 Thursday 4
23-OCT-03 Thursday 4
27-NOV-03 Thursday 4
25-DEC-03 Thursday 4
12 rows selected.
Now, when I write it like this to try and avoid a level of nesting it doesn't work:
select dt,
rj,
DAY,
SEQ
from (select trunc(sysdate,'year')+rownum-1 dt,
to_char(trunc(sysdate,'year')+rownum-1, 'd') rj,
to_char(trunc(sysdate,'year')+rownum-1, 'Day') "DAY",
row_number() over (partition by to_char(trunc(sysdate,'year')+rownum-1, 'mm') order by trunc(sysdate,'year')+rownum-1) "SEQ"
from all_objects
where rownum <= 365
and to_char(trunc(sysdate,'year')+rownum-1, 'd') = '5'
) rs
where seq = '4'
The "and to_char(trunc(sysdate,'year')+rownum-1, 'd') = '5'" within the inline statement doesn't seem to work. Can you let me know why?
Thanks.
January 23, 2003 - 1:21 pm UTC
like an atom bomb to crack a walnut isn't it?
given any date DT:
next_day( (trunc(dt,'mm')-1), 'THU' ) + 21
answers the question....
You cannot remove a level of nesting -- especially with analytics. they work on the results AFTER the predicate -- it would totally change the meaning.
Sort of like the difference between:
select * from ( select * from t order by x ) where rownum < 5;
gets the first rows AFTER sorting where as
select * from t where rownum < 5 order by x
gets five rows AND THEN sorts just those 5.
Apples and oranges
calculate fourth thursday of November
Cindy, January 23, 2003 - 1:00 pm UTC
thanks for the help..
to take this a couple of steps further I would like to find just the 4th Thursday of November, basically Thanksgiving and I would ultimately like to have this available to do in Oracle Forms..any ideas?
I did the following, but all_objects is only available for DBA..any other way?
select trunc(sysdate,'year')+r-1
from (select rownum r from all_objects where rownum <= 366)
where to_char(trunc(sysdate,'year')+r-1,'Mon')=('Nov')
and to_char(trunc(sysdate,'year')+r-1,'Dy')=('Thu')
and to_char(trunc(sysdate,'year')+r-1,'W')=(4);
January 23, 2003 - 1:23 pm UTC
1 select
2 next_day( (trunc(to_date('nov','mon'),'mm')-1), 'THU' ) + 21
3* from dual
ops$tkyte@ORA920> /
NEXT_DAY(
---------
27-NOV-03
does that.
All_objects is available to ALL USERS -- if not, your database is NOT SUPPORTED.
Cindy, January 23, 2003 - 3:35 pm UTC
Thanks!
Is it a good practice?
Joe, August 31, 2005 - 12:43 pm UTC
Tom,
I see this a lot in some of our codes.
........WHERE TRUNC(ctcl.number) = number_IN is that a good practice?
August 31, 2005 - 2:02 pm UTC
I'd probably rather say
where ctcl.number >= trunc(number_in) and ctcl.number < trunc(number_in)+1
(assuming positive numbers -- negative included, it would be different)
then, if ctcl.number is indexed, we can use the index and don't have to apply the trunc function to every instance of ctcl.number.
thanks!!!!!
A reader, September 06, 2005 - 3:58 pm UTC