Quaterly Data selection problem

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

thanksyou have asked this quarterly question three times now

received answers twice but have not replied whether the answers you already received were suitable

here is another answer

please, please let us know if this is suitable, don't just ignore us, or your future questions will go unanswered

this is my original answer, modified by ionut (http://dbforums.com/member.php?action=getinfo&userid=26346), then modified by me to add date range testing
select 'Q' + cast(cast( (month(period)+2) / 3 as integer ) as char(1))
+ '-' + year(period) as QuarterYear
, sum(amount) as SumAmount
from oper_sundata
where year(period)*12+month
between cast(@.startyyyymm as integer)
and cast(@.endyyyymm as integer)
group
by cast( (month(period)+2) / 3 as integer )
, year(period)
order
by cast( (month(period)+2) / 3 as integer )
, year(period)

rudy
http://r937.com/

Quaterly Data From Selected Range

Hi all,

I have one table with period data time and composite integer
now i want to select the sum(composite) for quaterly like
if user selects 200001 to 200303 i want all the quater which fall under the selection/
in this we have q1.q2.q3.q4 of 2000 and
q1.q2.q3.q4 of 2001 and q1.q2.q3.q4 of 2002 and q1 2003...i want all those quaters..each one row...
so how to do that let me know the query ...did you try this (http://dbforums.com/showthread.php?threadid=751460) ?

rudy|||Like R937 said (with one corection of mine), try this:

select year(period) as year,cast( (month(period)+2) / 3 as integer ) as quarter, sum(composite)
from cdh_price_gap
group by year(period),cast( (month(period)+2) / 3 as integer )

or

select year(period) as year,cast( ((month(period)-0.3)/ 3)+1 as integer ) as quarter, sum(composite)
from cdh_price_gap
group by year(period),cast( ((month(period)-0.3)/ 3)+1 as integer )

IONUT CALIN

PS
From what I understant you need you need separate sums for q1 2002 and q2 2003 (in this case you alo have to group by year of date field)

Good Luck!