Query Help - 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)

Query Help

Author

Topic

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-08-14 : 17:50:44

[code]
I need a query to generate the daily count based on the lastmodifydate and condition is even the count is 0 it should list the date in the result between from and to dates..

Ex:

Date Numbercount
-------- ---------
08/1/2012 1
08/2/2012 0
.
.
.
.
.
.
08/31/2012 15[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-14 : 18:00:57

use a calendar table and left join your table to that

something like


SELECT c.[date],COALESCE(Cnt,0) AS Cnt
FROM dbo.CalendarTable(@start,@end,0,0)c
LEFT JOIN (SELECT DATEADD(dd,DATEDIFF(dd,0,lastmodifydate),0) AS [date],COUNT(yourfield) AS Cnt
           FROM YourTable
           GROUP BY DATEADD(dd,DATEDIFF(dd,0,lastmodifydate),0)
         )t
ON t.[date]=c.[date]

CalendarTable function can be found in below link

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources