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

    Unanswered: Yearly Data from Selected Range

    Hi all,
    I have the Following Query
    It is working fine if the first month is 01 and Second month is 12
    but i want like this if the user selects 200103 to 200203 i want the
    value of composite should be added from 200103 to 200203 in one row
    and other row from 200203 to 200303.
    like this if the user selects from 200004 to 200304 i have to get 3 rows..
    that is Yearly data..so how can i modify the following query or any new query.


    select CONVERT(CHAR(4),period,112), sum(composite)
    from cdh_price_gap
    where CONVERT(CHAR(6),period,112) between '200101' and '200312'
    group by CONVERT(CHAR(4),period,112)
    order by CONVERT(CHAR(4),period,112)


    Thanks

  2. #2
    Join Date
    Mar 2003
    Location
    Indiana, USA
    Posts
    100
    This is an Wild Guess & Very Un-Tested at how to do it.

    select DATEDIFF ( Year , '2001-03-01' , period ), sum(composite)
    from cdh_price_gap
    where DATEDIFF ( Year , '2001-03-01' , period ) < 4
    group by DATEDIFF ( Year , '2001-03-01' , period )
    order by DATEDIFF ( Year , '2001-03-01' , period )


    Tim S

  3. #3
    Join Date
    Apr 2003
    Posts
    14

    Yearly Total

    Thanks Tim S for u r ans...
    but actaully that wont support my need.
    because my question is i want to select yearly data from the
    selected Range ...
    like i have a table by name A
    in that i have field Period it is datatime
    and Composite is integer...

    now if ur selectes range from 2001/03 to 2003/03
    i want to sum the composite value from 2001/03 to 2002/03
    and from 2002/04 to 2003/03....
    those two dates are not constant ....just for example ...
    user may select any range
    so how to do that...

    thanks

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what exactly does the user select? startyyyymm and endyyyymm? or startyyyy and endyyyy?

    rudy

  5. #5
    Join Date
    Apr 2003
    Posts
    14
    user selects yyyymm in both start and end date

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, here are the assumptions:

    - user passes two parameters, @startyyyymm and @endyyyymm
    - parameters are character strings
    - want to group data by a fiscal year
    - start of fiscal year is determined by the mm of @startyyyymm
    - fiscal year is named by the year it ends in
    Code:
    select case when substring(@startyyyymm,5,2)
                   > substring(convert(char(6),period,112),5,2)
                then year(period)
                else year(period)+1
            end as fiscalyear
         , sum(composite) as fiscalsumcomposite
      from cdh_price_gap
     where convert(char(6),period,112)
           between @startyyyymm and @endyyyymm
    group
        by fiscalyear
    if sql server does not allow grouping by a column alias, then write
    Code:
    ...
    group
        by case when substring(@startyyyymm,5,2)
                   > substring(convert(char(6),period,112),5,2)
                then year(period)
                else year(period)+1
            end
    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
  •