April 17, 2003 at 7:43 am
Hi all,
I have one table with oper_year and oper_month , those two are int.
Now i want to take the quaterly data from the table as per the user selection like this
If user selects....200001 to 200212 means in that we have 8 quaters...4 in 2001 and 4 in 2002 and if user selects the range from 200101 to 200209 it has 7 quaters..means in 2001..4 Quaters.. and in 2002 --3 Quaters....
i have the following query it is working but if i give the 200101 to 200209 it is shows only 6 quaters...what is the wrong in that if possible please correct it.....
SELECT CASE WHEN (oper_month) IN (1,2,3)THEN 'Q1-' + Cast(oper_year as varchar)
WHEN (oper_month) IN (4,5,6)THEN 'Q2-' + Cast(oper_year as varchar)
WHEN(oper_month) IN (7,8,9)THEN 'Q3-' + Cast(oper_year as varchar)
WHEN (oper_month) IN (10,11,12)THEN 'Q4-'+ Cast(oper_year as varchar)END
,sum(amount)
FROM oper_sundata
where oper_year between 2001 and 2002
and oper_month between 01 and 09
group by oper_year,
CASE WHEN (oper_month) IN (1,2,3)THEN 'Q1-' + Cast(oper_year as varchar)
WHEN (oper_month) IN (4,5,6)THEN 'Q2-' + Cast(oper_year as varchar)
WHEN (oper_month) IN (7,8,9)THEN 'Q3-' + Cast(oper_year as varchar)
WHEN (oper_month) IN (10,11,12)THEN 'Q4-' + Cast(oper_year as varchar)End
order by oper_year,
CASE WHEN (oper_month) IN (1,2,3)THEN 'Q1-' + Cast(oper_year as varchar)
WHEN (oper_month) IN (4,5,6)THEN 'Q2-' + Cast(oper_year as varchar)
WHEN (oper_month) IN (7,8,9)THEN 'Q3-' + Cast(oper_year as varchar)
WHEN (oper_month) IN (10,11,12)THEN 'Q4-' + Cast(oper_year as varchar)End
thanks
April 17, 2003 at 8:02 am
where oper_year between 2001 and 2002
and oper_month between 01 and 09
The problem is that you are only selecting months 01 to 09 so you are not getting anything for the 4th quarter 2001.
One way to correct the problem would be to use datetime fields (or smalldatetime) and then use date functions. Another way is to concatenate the year and month into a single column.
Jeremy
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply