report query - 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)

report query

Author

Topic

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-05-04 : 14:34:02

I need to rewrite the below query to get the expected output.

Select Dateadd(day, Datediff(day, 0, CreatedOn), 0) as CreatedOn, Count(Code) as [Count]
from table
where CreatedOn > '2011-10-01 00:00:00.000'
group by Dateadd(day, Datediff(day, 0, CreatedOn), 0)
order by 1

I got the expected output with above query when the there is count more than 1.but i need the output with dates where the count is 0.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-04 : 15:04:10

You need a calendar table and then left join to the calendar table. If you don't have a calendar table, you can create one like this:

CREATE TABLE #tmpCalendar(dt DATE NOT NULL PRIMARY KEY CLUSTERED);
;WITH cte(dt) AS
( SELECT CAST('20111001' AS date) UNION ALL SELECT DATEADD(dd,1,dt) FROM cte WHERE dt < '20111031')
INSERT INTO #tmpCalendar SELECT * FROM cte;

Then your query would be

SELECT
	c.Dt AS CreatedOn,
	COUNT(Code) AS [Count]
FROM
	#tmpCalendar c 
	LEFT JOIN  YourTable t ON CAST(t.CreatedOn AS DATE) = c.Dt
WHERE
	t.CreatedOn > '20111001'
GROUP BY
	CAST(t.CreatedOn AS DATE)
ORDER BY
	1

Since you are on SQL 2008, I changed your "Dateadd(day, Datediff(day, 0, CreatedOn), 0)" to "CAST(CreatedOn as DATE)" which works just as well, and is probably easier on the eyes.

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-05-04 : 16:31:08

Thanks ..

I was trying with the below query but output is not showing all months..
Please help..


declare @TableCal table ( 
	Month1 Varchar(25) NOT NULL  )

Insert Into @TableCal (Month1)

(Select	'January'
union all
Select	'February'
union all
Select	'March'
union all
Select	'April'
union all
Select	'May'
union all
Select	'June'
union all
Select	'July'
union all
Select	'August'
union all
Select	'September'
union all
Select	'October'
union all
Select	'November'
union all
Select	'December')

SELECT DATENAME(MONTH, createdon) AS [MonthName] ,COUNT(*) AS MonthCount
FROM @TableCal a Left join dbo.table on a.Month1 = DATENAME(MONTH, createdon)
where createdon > '2011-01-01 00:00:00.000'
GROUP BY DATENAME(MONTH, createdon)
ORDER BY MonthName

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-04 : 17:53:28

[code]SELECT a.Month1 AS [MonthName] ,COUNT(*) AS MonthCount
FROM @TableCal a Left join dbo.table on a.Month1 = DATENAME(MONTH, createdon)
AND createdon > '2011-01-01 00:00:00.000'
GROUP BY a.Month1
ORDER BY MonthName[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources