Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Is there a better way to write this sql?
On 24 Apr 2007 09:13:02 -0700, Anthony Smith <mrsmithq_at_hotmail.com>
wrote:
>select other_columns
> (case when TO_CHAR(expiration_dttm, 'YYYY/MM/DD') between
>'2006/06/01' and
>'2006/08/31' then 1
>when TO_CHAR(expiration_dttm, 'YYYY/MM/DD') between '2006/09/01' and
>'2006/11/31' then 2
>when TO_CHAR(expiration_dttm, 'YYYY/MM/DD') between '2006/12/01' and
>'2007/2/29' then 3
>when TO_CHAR(expiration_dttm, 'YYYY/MM/DD') between '2007/3/01' and
>'2007/5/31' then 4 end) qtr
>from common.employee_assignment order by qtr
>
>I do not want to hardcode for each fiscal year. Is there a way where I
>don't have to specify year. And notice since the quarters start with
>June 1st, my fiscal year will need to be figured out as well.
define startdt = '&1'
select other_columns
case when expiration_dttm between to_date('2006/06/01','YYYY/MM/DD')
and last_day(add_months(to_date('2006/06/01','YYYY/MM/DD'),2)) then 1
when expiration_dttm between
add_months(to_date('2006/06/01','YYYY/MM/DD'),3) and
and last_day(add_months(to_date('2006/06/01','YYYY/MM/DD'),3+2)) then
2
when expiration_dttm between
add_months(to_date('2006/06/01','YYYY/MM/DD'),6) and
and last_day(add_months(to_date('2006/06/01','YYYY/MM/DD'),6+2)) then
3
when expiration_dttm between
add_months(to_date('2006/06/01','YYYY/MM/DD'),9) and
and last_day(add_months(to_date('2006/06/01','YYYY/MM/DD'),9+2)) then
3
replace '2006/06/01' by '&startdt'
-- Sybrand Bakker Senior Oracle DBAReceived on Tue Apr 24 2007 - 12:12:30 CDT