Count number of incidents per day help

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 2005 Forums

Transact-SQL (2005)

Count number of incidents per day help

Author

Topic

lacelacelace
Starting Member

3 Posts

Posted - 2011-01-04 : 20:02:41

hi all,

i need help with my query..

i would like to show the number of incidents PER DAY of every month..
for example :

1 2 3 4 5 6 7 8 9 10.........31 <-day#
---------------------------------------
Jan 2 0 4 1 1 0 0 0 1 3 .........1 >
Feb 2 0 4 1 1 0 0 0 1 3 .........1 >
Mar 2 0 4 1 1 0 0 0 1 3 .........1 > # of incidents per day
.
.
Dec 2 0 4 1 1 0 0 0 1 3 .........1 >

So far, i am getting this type of result.

(Month) (Day) (# of incidents)
Jan 2 5
Jan 3 1
Jan 5 3
Feb 1 3
Feb 4 1
.
.
.

any suggestions how can achieve the look that i want? thank you in advance

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-01-04 : 20:20:32

I can give some suggestions. This would be really easy to do in Excel with a Pivot table, but you can use the PIVOT operator in SQL 2005. This will get you started.

Jim

DECLARE @Table Table (Mth varchar(3),Dy tinyint,incidents int)

INSERT INTO @Table
SELECT 'Jan', 2, 5 UNION ALL
SELECT 'Jan', 3 ,1UNION ALL
SELECT 'Jan', 5, 3UNION ALL
SELECT 'Feb', 1, 3UNION ALL
SELECT 'Feb', 4, 1

SELECT Mth ,isnull([1],0),isnull([2],0),isnull([3],0),isnull([4],0),isnull([5],0)
FROM

(select mth,Dy,Incidents
from @table
) t
pivot
( sum(incidents) for dy in ([1],[2],[3],[4],[5])
) pvt

Everyday I learn something that somebody else already knew

lacelacelace
Starting Member

3 Posts

Posted - 2011-01-04 : 20:39:05

HI Jim, thanks for replying

it says

The Declare cursor SQL construct or statement is not supported.

:(

BTW this is my query..

SELECT DATEADD(day, DATEDIFF(day, 0, createdon), 0) AS CreatedOn, COUNT(createdon) AS Count, DATENAME(MONTH, createdon) AS MonthName,
DAY(createdon) AS DAY
FROM FilteredIncident AS CRMAF_FilteredIncident
WHERE (YEAR(createdon) = 2008) AND (subjectidname = 'Infrastructure Services')
GROUP BY DATEADD(day, DATEDIFF(day, 0, createdon), 0), DATENAME(MONTH, createdon), DAY(createdon)
ORDER BY createdon

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-01-04 : 23:18:24

quote:
"The Declare cursor SQL construct or statement is not supported."]

can you show us the code that gives that error ?

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

lacelacelace
Starting Member

3 Posts

Posted - 2011-01-04 : 23:33:51

Hi Kh, thanks for replying

i just pasted Jim's code

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources