Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2003
    Posts
    14

    Unanswered: 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



    thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you 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, then modified by me to add date range testing
    Code:
    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/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •