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