Count number of records added per day

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

General SQL Server Forums

New to SQL Server Programming

Count number of records added per day

Author

Topic

meberg66219
Yak Posting Veteran

65 Posts

Posted - 2010-12-02 : 08:52:32

I have a table that I need to determine how many records are added for each weekday.

SELECT DISTINCT
MONTH(dbo.Systudent.DateAdded) AS AddDate,
COUNT(dbo.Systudent.SyStudentID) AS NewStudents
FROM
dbo.SyStudent
GROUP BY
DateAdded

I believe the above is pulling in the total count per month of newly inserted records. As I get data returns similar to the following:

AddDate NewStudents
12 6
11 5
4 8

Could someone please suggest how I might be able to get the information per weekday?

Thank you.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-02 : 09:01:41

[code]SELECT dbo.Systudent.DateAdded AS AddDate,
COUNT(dbo.Systudent.SyStudentID) AS NewStudents
FROM dbo.SyStudent
GROUP BY
DateAdded[/code]

meberg66219
Yak Posting Veteran

65 Posts

Posted - 2010-12-02 : 09:38:54

More specifically, I need it per day of the week in a manner that keeps track on a continuous basis so that I can graph the progress of number of students added per day.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-02 : 09:41:15

[code]SELECT DATEPART(dw, DateAdded) AS AddDate,
COUNT(SyStudentID) AS NewStudents
FROM dbo.SyStudent
GROUP BY
DATEPART(dw, DateAdded)
[/code]

meberg66219
Yak Posting Veteran

65 Posts

Posted - 2010-12-02 : 10:21:53

This works very well. However it is pulling the total count for the entire history of the database and not just for the current week we are in. Do you know of a way to make it pull only for the current week and a way to set it up so it pulls for the week following, etc.?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-02 : 10:41:45

filter the date range you want with a WHERE clause

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-12-02 : 10:42:04

quote:

Originally posted by russell

SELECT	DATEPART(dw, DateAdded) AS AddDate, 
	COUNT(SyStudentID) AS NewStudents
WHERE YEAR(DateAdded) = YEAR(GETDATE())
AND DATEPART(dw, DateAdded) = DATEPART(dw, GETDATE())
FROM	dbo.SyStudent
GROUP BY
	DATEPART(dw, DateAdded)

Maybe this?

No, you're never too old to Yak'n'Roll if you're too young to die.

meberg66219
Yak Posting Veteran

65 Posts

Posted - 2010-12-02 : 11:41:47

Thank you to both of you. This is much closer to where I want to be. I'm still having issues with getting all of the data to pull back with the Where Clause and also trying to figure out a way to make it automatically update for each week.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources