Post reply

running total possible??

  • March 27, 2013 at 8:45 am

    #270466

    Hi

    I have a view which selects name and date say to make it short...

    each name can contain more than one record

    I want to keep a running total of names by month

    example

    name1 1/1/12

    name1 1/2/12

    name3 1/2/12

    name3 2/1/12

    name1 2/1/13

    The end results I want to show

    January(or 1) =2(2 names)

    February = 2

    Im also writing this out to SSRS so maybe a formuala ?

    Thanks

    Joe

  • March 27, 2013 at 9:06 am

    #1601206

    What are you trying to show? Based on your post I don't see any reason for a running total.

  • March 27, 2013 at 9:09 am

    #1601208

    I agree with Lynn.. this doesn't seem like a running total issue.

    This would just as easily get what you want but I'm curious why you don't care about the year portion of the date.

    select count(source.col1), DATEPART(month,datecol)

    from

    (

    select 'name1' col1, '1/1/12' dateCol

    union

    select 'name1' col1, '1/2/12' dateCol

    union

    select 'name3' col1,'1/2/12' dateCol

    union

    select 'name3' col1, '2/1/12' dateCol

    union

    select 'name1' col1, '2/1/13' dateCol

    ) source

    group by DATEPART(month,datecol)

  • March 27, 2013 at 9:09 am

    #1601210

    Hi Lynn,

    I came up with his but I think it's kind of a crappy way to do it...

    I have a case for each month

    CASE WHEN DATEPART(mm,STARTTIME) = 1 THEN (Client.id) ELSE NULL END AS JanClients ...etc

    if SSRS I use expression =COUNTDISTINCT(Fields!JanClients.Value, "DataSet2")

  • March 27, 2013 at 9:10 am

    #1601212

    Parameters are for FY

    I want a client count for each month

  • March 27, 2013 at 9:11 am

    #1601213

    Then the sql I posted would be sufficient for such a task, I believe.

  • March 27, 2013 at 11:15 am

    #1601275

    Erin Ramsay (3/27/2013)

    Then the sql I posted would be sufficient for such a task, I believe.

    Not quite:

    select count(distinct source.col1), DATEPART(month,datecol)

    from

    (

    select 'name1' col1, '1/1/12' dateCol

    union

    select 'name1' col1, '1/2/12' dateCol

    union

    select 'name3' col1,'1/2/12' dateCol

    union

    select 'name3' col1, '2/1/12' dateCol

    union

    select 'name1' col1, '2/1/13' dateCol

    ) source

    group by DATEPART(month,datecol)

Viewing 7 posts - 1 through 6 (of 6 total)

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