cumulative count by day - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

cumulative count by day

Author

Topic

dgaylor
Yak Posting Veteran

54 Posts

Posted - 2005-09-20 : 14:12:35

I have a table that has registration data in it. I need to return the cumulative head-count for each day before the event starts.

I currently have a select statement that is returning the individual counts per day from event:

DaysFromEvent HeadCount
46 1
45 2
44 3

But what I really need is:

DaysFromEvent HeadCount
46 1
45 3
44 6

Can anyone help? Thanks in advance

Also, when the number of days from the event is greater than 60 then I need those day counts to be rolled up into one value, so I need a 60+ DaysFromEvent value.

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

CREATE TABLE [dbo].[Reg] (
[RegDate] [datetime] NOT NULL ,
[Paid] [bit] NOT NULL
)
GO

INSERT reg (regdate, paid) VALUES ('2005-09-17', 0)
INSERT reg (regdate, paid) VALUES ('2005-09-17', 1)
INSERT reg (regdate, paid) VALUES ('2005-09-18', 0)
INSERT reg (regdate, paid) VALUES ('2005-09-18', 1)
INSERT reg (regdate, paid) VALUES ('2005-09-18', 1)
INSERT reg (regdate, paid) VALUES ('2005-09-19', 1)
INSERT reg (regdate, paid) VALUES ('2005-09-19', 1)
INSERT reg (regdate, paid) VALUES ('2005-09-19', 1)
GO

SELECT
DATEDIFF(dd, Reg.RegDate, cast('11/2/2005' as datetime) ) as DaysFromEvent, COUNT(*) As HeadCount
FROM
Reg
WHERE
Reg.Paid = 1
AND (DATEDIFF(dd, Reg.RegDate, cast('11/2/2005' as datetime) ) >= 0)
GROUP BY
DATEDIFF(dd, Reg.RegDate, cast('11/2/2005' as datetime) )
ORDER BY
DATEDIFF(dd, Reg.RegDate, cast('11/2/2005' as datetime) ) desc
GO

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-21 : 01:25:23

Do you want to show this in your Presentation layer like Reports?
If so, use RunningTotal feature of Reports. You can do this with sql query but if there are thousands of data you will get performance issues

Here is an sql query

Select DaysFromEvent (Select sum(HeadCount) from yourTable where DaysFromEvent <=T.DaysFromEvent)
from yourTable T

Madhivanan

Failing to plan is Planning to fail

dgaylor
Yak Posting Veteran

54 Posts

Posted - 2005-09-21 : 01:59:57

Hi, this is something I would rather have sql return, if possible, rather than handling it in the presentation layer.

I am not sure I understand the suggested sql code, it doesn't use my table structure. Thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-21 : 02:12:07

>>if possible, rather than handling it in the presentation layer.

No you should consider doing this in your presentation layer
I just gave an idea of how to do Running Total thru query
What you need is something like this



Select * into #t from
	(
	SELECT DATEDIFF(dd, Reg.RegDate, cast('11/2/2005' as datetime) ) as DaysFromEvent, COUNT(*) As HeadCount
	FROM Reg WHERE  Reg.Paid = 1
	AND (DATEDIFF(dd, Reg.RegDate, cast('11/2/2005' as datetime) ) >= 0)
	GROUP BY DATEDIFF(dd, Reg.RegDate, cast('11/2/2005' as datetime) ) 
	ORDER BY DATEDIFF(dd, Reg.RegDate, cast('11/2/2005' as datetime) ) desc
	) T

Select DaysFromEvent (Select sum(HeadCount) from #t where DaysFromEvent <=T.DaysFromEvent)
from #t T

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources