Post reply

Trying to do a cumulative total based on year/month

  • February 4, 2006 at 8:31 am

    #168065

    Hi,

    I can't seem to figure this out.  I'm trying to create a cumulative qty total for each year and month.  For example:

    Partnumber2006,12006,22006,32006,4
    123413510
    Mth Qty 1Mth Qty 2Mth Qty 2Mth Qty 5

    For given partnumber 1234 for month 1 there was a qty of 1, for month 2 there was a qty of 2 since I'm trying to add a cumulative sum the qty would be 3 1+2.  The caveat is that the year begins in 1999 to present for each month.  I would have a running cumulative since 1999 - 2006 for each month.  What is the best way to accomplish this task?

     

    Thanks,

  • February 4, 2006 at 12:47 pm

    #618856

    Your DDL would be helpful.

    Mathew J Kulangara
    sqladventures.blogspot.com

  • February 6, 2006 at 9:14 pm

    #619150

    Something for your reference.

    ---------------------------------------------------------------------------------------

    create table #part

    (

    PartNumberint,

    Perioddatetime,

    Qtyint

    )

    insert into #part

    select1234, '2006-01-01', 1union all

    select1234, '2006-02-01', 2union all

    select1234, '2006-03-01', 2 union all

    select1234, '2006-04-01', 5union all

    select5678, '2006-01-01', 2union all

    select5678, '2006-03-01', 4union all

    select 5678, '2006-04-01', 8

    selectPartNumber,

    sum(case when Period = '2006-01-01' then Qty else 0 end) as [MTD-2006-Jan],

    sum(case when Period = '2006-02-01' then Qty else 0 end) as [MTD-2006-Feb],

    sum(case when Period = '2006-03-01' then Qty else 0 end) as [MTD-2006-Mar],

    sum(case when Period = '2006-04-01' then Qty else 0 end) as [MTD-2006-Apr]

    from#part

    group by PartNumber

    selectPartNumber,

    sum(case when Period <= '2006-01-01' then Qty else 0 end) as [YTD-2006-Jan],

    sum(case when Period <= '2006-02-01' then Qty else 0 end) as [YTD-2006-Feb],

    sum(case when Period <= '2006-03-01' then Qty else 0 end) as [YTD-2006-Mar],

    sum(case when Period <= '2006-04-01' then Qty else 0 end) as [YTD-2006-Apr]

    from#part

    group by PartNumber

    drop table #part

  • February 7, 2006 at 6:55 am

    #619235

    Hi,

    Thanks for your reference snippet.  Worked great.

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

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