Пересылка: ceiling problem - How to find which quarter

["Shulzhenko Vasyl" <vasilis@softline.kiev.ua>]

-----Исходное сообщение-----
От: Jonathan Leffler <jleffler@informix.com>
Группы: comp.databases.informix
Дата: 27 января 1999 г. 2:11
Тема: Re: ceiling problem - How to find which quarter


>
>On Tue, 26 Jan 1999, Girish Kagrana wrote:
>> Our requirement was to findout the Quarter( i.e. 1,2,3,4) for a given date
>> in that year( Year start with 1st Jan)
>>
>> SELECT CEILING(MONTH(ip_date)/3) ......
>> for above stmt. if date is 1st March then select stmt. o/p is '2' instead of
>> '1' as it should be first quarter.
>[...except that Informix takes CEILING(10) == 11...]
>
>Mapping of month numbers is:
> 1,2,3 => 1
> 4,5,6 => 2
> 7,8,9 => 3
> 10,11,12 => 4
>
>What about:
>
> SELECT TRUNC((MONTH(ip_date) + 2)/3)
>
>For January: MONTH =  1, (MONTH(ip_date) + 2)/3 = 1.00, TRUNC(...) = 1
>For February: MONTH =  2, (MONTH(ip_date) + 2)/3 = 1.33, TRUNC(...) = 1
>For March: MONTH =  3, (MONTH(ip_date) + 2)/3 = 1.67, TRUNC(...) = 1
>For April: MONTH =  4, (MONTH(ip_date) + 2)/3 = 2.00, TRUNC(...) = 2
>....
>For December: MONTH = 12, (MONTH(ip_date) + 2)/3 = 4.67, TRUNC(...) = 4
>
>Or you could write an SP to do the calculation and use that -- it would be
>clearer to say:
>
> SELECT quarter_number(ip_date)
>
>and is also more reliable, especially if your year ever changes from the
>1st January alignment for quarter ends -- funnier things have happened!
>
>CREATE PROCEDURE quarter_number(d DATE) RETURNING INTEGER;
> RETURN TRUNC((MONTH(d) + 2) / 3);
>END PROCEDURE;
>
>> Currently We are using the following solution, if any other better
>> solution is avail. then let me know, (It should be a single SELECT
>> stmt)
>>
>> SELECT
>> DECODE(MOD(MONTH(ip_date),3),0,MONTH(ip_date)/3,FLOOR(MONTH(ip_date)/3)+1)
>
>If you prefer to use FLOOR instead of TRUNC, that will work too.
>
>Yours,
>Jonathan Leffler (jleffler@informix.com) #include <wish/I/was/skiing.h>
>Guardian of DBD::Informix v0.60 (v0.61_02) -- http://www.perl.com/CPAN
>Informix IDN for D4GL & Linux -- http://www.informix.com/idn
>