counting rows created in last month

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

counting rows created in last month

Author

Topic

oap
Yak Posting Veteran

60 Posts

Posted - 2012-09-09 : 11:24:19

Hi.

I did my research before posting this, tried a few sample code snippets but none of them worked.

I have a table with rows of users...

The dtregister is a Time/Date field in SQL 2005 that shows when the user registered on the site. I would like to run a query to show how many users joined this month.

The code I've found so far will either count back xx number of days, or do all months. I would like to grab the number only for the CURRENT month. I'm coding in ASP by the way.

Could anyone help?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-09 : 12:02:46

[code]
SELECT *
FROM table
WHERE DTREGISTER >= DATEADD(mm,DATEDIFF(mm,0,GETDATE())
AND DTREGISTER < DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

oap
Yak Posting Veteran

60 Posts

Posted - 2012-09-09 : 20:28:14

Thank you for the reply.

s="SELECT * FROM users WHERE DTREGISTER >= DATEADD(mm,DATEDIFF(mm,0,GETDATE()) AND DTREGISTER < DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)"
Set oRS = oConn.Execute(s)

results in
Incorrect syntax near the keyword 'AND'.

Also how would I calculate the SUM of the results, rather than returning the actual records. I wish to displa a message: xxx new members joined today.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-09 : 21:46:27

[code]SELECT count(*)
FROM table
WHERE DTREGISTER >= DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
AND DTREGISTER < DATEADD(month, DATEDIFF(month, 0, GETDATE()) + 1, 0)[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-09 : 22:42:29

quote:
Originally posted by oap

Thank you for the reply.

s="SELECT * FROM users WHERE DTREGISTER >= DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) AND DTREGISTER < DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)"
Set oRS = oConn.Execute(s)

results in
Incorrect syntax near the keyword 'AND'.

Also how would I calculate the SUM of the results, rather than returning the actual records. I wish to displa a message: xxx new members joined today.

there was a typo

to get sum of results use COUNT(*) or SUM(1) instead of just *

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

oap
Yak Posting Veteran

60 Posts

Posted - 2012-09-09 : 22:43:08

khtan, it worked perfect I just had to include a "count(*) as mycount".

Thank you both.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources