>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.
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
d@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
-----------------------------------------------Reply-----------------------------------------------
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
Add to del.icio.us |
Digg this |
Stumble it |
Powered by Megasolutions Inc
