Count ALL Days - 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 2008 Forums

Transact-SQL (2008)

Count ALL Days

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2012-02-24 : 06:47:16

Hello,

I have a table with a column Created of type DateTime.

I need to count the number of posts created by DAY.

In some days there might not exist posts.
So I also need those days in the list but with Count = 0.

How can I do this?

Thank you,
Miguel

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-24 : 07:56:03

You will need a calendar table to do this. If you do, you can get the counts as shown below:

SELECT
	c.[Date],
	COUNT(*)
FROM
	CalendarTable c
	LEFT JOIN YourTable t ON CAST(t.Created_Date AS DATE) = c.Date
GROUP BY
	c.[Date]
ORDER BY
	c.[Date];

Here is an example of how to create a calendar table: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519&SearchTerms=F_TABLE_DATE You would not need all the information in that table. You can create a simple one like this:

CREATE TABLE CalendarTable ( [Date] DATE NOT NULL PRIMARY KEY CLUSTERED);

DECLARE @startDate DATETIME, @endDate DATETIME;
SET @startDate = '20110101';
SET @endDate = '20111231';
;WITH Calendar(dt) AS
(
	SELECT @startDate
	UNION ALL
	SELECT DATEADD(dd,1,dt)
	FROM Calendar
	WHERE dt <@endDate
)
INSERT INTO CalendarTable SELECT dt FROM Calendar
OPTION (MAXRECURSION 0);

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources