Re: Is there a better way to write this sql?

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

.