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

From: Thorsten Kettner <thorsten.kettner_at_web.de>
Date: 26 Apr 2007 00:44:53 -0700
Message-ID: <1177573493.599011.291600@b40g2000prd.googlegroups.com>

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

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 Received on Thu Apr 26 2007 - 02:44:53 CDT