Re: FY Range Determination
Date: 1998/01/03
Message-ID: <34b074f6.10075788_at_inet16>#1/1
On 2 Jan 1998 20:36:39 GMT, "J.R. Rushmeyer" <rushmeyer_at_ada.state.oh.us> wrote:
>Has anyone written a script or procedure determining if the SysDate fell
>within a certain FY range?
>For example, if the SysDate is less than July 1,1998, then the start date
>for the FY is
>7/1/97 (the SysDate year less 1)
>and the End Date is June 30,98 (the SysDate actual year)
>
>If the SysDate is greater than July 1, 1998 then the start date if
>7/1/1998 (the current SysDate year)
>and the End Date is June 30, 1999 (the SysDate year PLUS 1)
>
>I need to set up a procedure that will run that Query but am not sure how
>to do it in SQL. If anyone has done this before (in a Procedure File) any
>help would be appreciated.
>Thanks
>J.R. Rushmeyer
>Ohio Dept of Alc and Drugs
Not sure what exactly you are looking for but one of the columns in the following query probably is it:
select to_char(hiredate,'mm') mon,
round( hiredate, 'year' ),
sign( round(hiredate,'year') - hiredate) sg,
decode( sign( round(hiredate,'year') - hiredate ),
1, 'First Half',
0, 'First Half',
-1, 'Second Half',
'Unknown' ) yr_part,
add_months(round( hiredate, 'year' ),-6) start_date,
hiredate,
add_months(round( hiredate, 'year' ),6)-1 end_date
from emp
/
MON ROUND(HIR SG YR_PART START_DAT HIREDATE END_DATE
--- --------- ---------- --------------- --------- --------- --------- 12 01-JAN-81 1 First Half 01-JUL-80 17-DEC-80 30-JUN-81 02 01-JAN-81 -1 Second Half 01-JUL-80 20-FEB-81 30-JUN-81 02 01-JAN-81 -1 Second Half 01-JUL-80 22-FEB-81 30-JUN-81 04 01-JAN-81 -1 Second Half 01-JUL-80 02-APR-81 30-JUN-81 09 01-JAN-82 1 First Half 01-JUL-81 28-SEP-81 30-JUN-82 05 01-JAN-81 -1 Second Half 01-JUL-80 01-MAY-81 30-JUN-81 06 01-JAN-81 -1 Second Half 01-JUL-80 09-JUN-81 30-JUN-81 12 01-JAN-83 1 First Half 01-JUL-82 09-DEC-82 30-JUN-83 11 01-JAN-82 1 First Half 01-JUL-81 17-NOV-81 30-JUN-82 09 01-JAN-82 1 First Half 01-JUL-81 08-SEP-81 30-JUN-82 01 01-JAN-83 -1 Second Half 01-JUL-82 12-JAN-83 30-JUN-83 12 01-JAN-82 1 First Half 01-JUL-81 03-DEC-81 30-JUN-82 12 01-JAN-82 1 First Half 01-JUL-81 03-DEC-81 30-JUN-82 01 01-JAN-82 -1 Second Half 01-JUL-81 23-JAN-82 30-JUN-82
The round function on a date will round it up or down, and since you convienently picked June 30/July 1 as the cutoff, we can use it.
To find the part of the FY the date falls into (first part/second part), all we have to do is compare the round(date,'year') to the date in question. To do this, we use SIGN which returns -1 (less then), 0 (equal), 1 (greater then). We can use this to easily see what part of the year we are in (First half or second half). If you wanted to know if you were in the first/second half of the current FY, you are done.
If you wanted to know the start date/end date of the current FY, then all you need to do is ROUND(date,'YEAR') and add/substr 6 months to/from it (adjusting by 1 day for the end_date). Thats what
add_months(round( hiredate, 'year' ),-6) start_date,
add_months(round( hiredate, 'year' ),6)-1 end_date
does. the date in question is the HIREDATE column. We round it off to the nearest year, add/sub 6 months to/from it (adjusting for end date) and you have the start/end dates.
Hope this helps....
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sat Jan 03 1998 - 00:00:00 CET