running total possible??

Question

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

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

Lynn Pettis SSC Guru Points: 442467 More actions Follow · Answer 1

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

Erin Ramsay SSCrazy Eights Points: 9728 More actions Follow · Answer 2

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)

jbalbo SSCertifiable Points: 7583 More actions · Answer 3

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")

jbalbo SSCertifiable Points: 7583 More actions · Answer 4

Parameters are for FY

I want a client count for each month

Erin Ramsay SSCrazy Eights Points: 9728 More actions Follow · Answer 5

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

Lynn Pettis SSC Guru Points: 442467 More actions Follow · Answer 6

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)

Cookies on SQLServerCentral