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.

On 24 Apr 2007 09:13:02 -0700, Anthony Smith <mrsmithq@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 DBA

Anthony Smith 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.
>

It looks like your fiscal year starts June first. One approach is to
just look at the month of the year as follows:

select other_columns
(case when TO_CHAR(expiration_dttm, 'MM') between '06' and '08' then 1
when TO_CHAR(expiration_dttm, 'MM') between '09' and '11' then 2
when TO_CHAR(expiration_dttm, 'MM') = '12' then 3
when TO_CHAR(expiration_dttm, 'MM') between '01' and '02' then 3
when TO_CHAR(expiration_dttm, 'MM') between '03' and '05' then 4

end) qtr
from common.employee_assignment order by qtr

In the manner above, you just look for your mapping of the month to its
specific quarter, regardless of the calendar year.

Perhaps a more elegant solution is to do something more like this:

SELECT other_columns,MOD(ADD_MONTHS(expiration_dttm,-5),4)+1 AS qtr
FROM common.employee_assignment ORDER BY qtr;

You might have to play around with the end cases to see if the above
works exactly.....but it is a start.

HTH,
Brian

--
================================================== =================

Brian Peasland
dba@nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

--
Posted via a free Usenet account from http://www.teranews.com

On 24 Apr., 18:13, Anthony Smith <mrsmi...@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

This should be '2007/03/01' and '2007/05/31' instead of '2007/3/01'
and '2007/5/31' .

Is that supposed to continue with

when TO_CHAR(expiration_dttm, 'YYYY/MM/DD') between '2007/06/01'
and
'2007/08/31' then 5 end ?

Then the formula would be

select
(to_number(to_char(expiration_dttm,'YYYY')) - 2006) * 4 +
case to_char(expiration_dttm,'MM')
when '01' then -1
when '02' then -1
when '03' then 0
when '04' then 0
when '05' then 0
when '06' then 1
when '07' then 1
when '08' then 1
when '09' then 2
when '10' then 2
when '11' then 2
when '12' then 3
end as qtr
from dual