Post reply

Calculating 3 highest consecutive months spend over a period of a year

  • July 10, 2008 at 4:31 am

    #184494

    Hi,

    does any have any ideas on how to calculate the 3 highest consecutive months spend over a period of a year ??

    e.g for the following data

    Year Month Spend

    2008 Jan 100

    2008 Feb 200

    2008 Mar 100

    2008 Apr 400

    2008 May 200

    2008 Jun 100

    2008 Jul 300

    2008 Aug 100

    2008 Sep 200

    2008 Oct 300

    2008 Nov 400

    2008 Dec 100

    The 3 highest consecutive months would be Sep ~ Nov with a total of 900 in spend.

    The 3 months should not be restricted by quarters, but rather any any 3 consecutive months over the data period.

    Any ideas on how to calculate this other than using a cursor ??

    Thanks in advance for any help 🙂

  • July 10, 2008 at 5:05 am

    #840724

    See if this helps

    create table mytable(yr int, mn char(3), Spend int)

    insert into mytable(yr,mn,Spend)

    select 2008, 'Jan' , 100 union all

    select 2008, 'Feb' , 200 union all

    select 2008, 'Mar' , 100 union all

    select 2008, 'Apr' , 400 union all

    select 2008, 'May' , 200 union all

    select 2008, 'Jun' , 100 union all

    select 2008, 'Jul' , 300 union all

    select 2008, 'Aug' , 100 union all

    select 2008, 'Sep' , 200 union all

    select 2008, 'Oct' , 300 union all

    select 2008, 'Nov' , 400 union all

    select 2008, 'Dec' , 100;

    with cte as (

    select yr,mn,Spend,

    row_number() over(order by cast(cast(yr as varchar(4))+' ' + mn as datetime)) as rn

    from mytable)

    select top 1 t1.yr as [Start Year],t1.mn as [Start Month],sum(t2.Spend) as [3 Month Spend]

    from cte t1

    inner join cte t2 on t2.rn between t1.rn and t1.rn+2

    group by t1.yr,t1.mn

    having count(*)=3

    order by sum(t2.Spend) desc

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • July 10, 2008 at 5:09 am

    #840727

    You beat me...

    you can also do it like this:

    SELECT *,mSpent + (SELECT mSpent FROM @tmpTable WHERE nMonth = t1.nMonth + 1) + (SELECT mSpent FROM @tmpTable WHERE nMonth = t1.nMonth + 2) As Total FROM @tmpTable as t1

    Order by Total Desc

  • July 10, 2008 at 5:23 am

    #840732

    Great,

    Thanks for the quick replies 😀

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply