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

SQL Server Development (2000)

Query Help

Author

Topic

JTProg
Starting Member

24 Posts

Posted - 2007-01-18 : 12:17:37

I've built a query to track the amount of errors found with a particular user. How can I display data like this:
Month Year UserName January February ......

Instead of like this:
Month Year UserName January 2006 January 2007 .........

Here is the query I have so far.
Declare @UserName Varchar(20)

Set @UserName = 'Clarice'

SELECT datepart(m, dbo.tblErrors.ErrorDate) AS 'Monthly', datepart(yyyy, dbo.tblErrors.ErrorDate) AS 'Year', dbo.Main.UserName,
SUM(Case when datepart(yyyy, dbo.tblErrors.ErrorDate) = 2006 AND datepart(m, dbo.tblErrors.ErrorDate) = 1 Then 1 Else 0 End)as 'January',
SUM(Case when datepart(yyyy, dbo.tblErrors.ErrorDate) = 2006 AND datepart(m, dbo.tblErrors.ErrorDate) = 2 Then 1 Else 0 End)as 'February',
SUM(Case when datepart(yyyy, dbo.tblErrors.ErrorDate) = 2006 AND datepart(m, dbo.tblErrors.ErrorDate) = 3 Then 1 Else 0 End)as 'March',
SUM(Case when datepart(yyyy, dbo.tblErrors.ErrorDate) = 2006 AND datepart(m, dbo.tblErrors.ErrorDate) = 4 Then 1 Else 0 End)as 'April',
SUM(Case when datepart(yyyy, dbo.tblErrors.ErrorDate) = 2006 AND datepart(m, dbo.tblErrors.ErrorDate) = 5 Then 1 Else 0 End)as 'May',
SUM(Case when datepart(yyyy, dbo.tblErrors.ErrorDate) = 2006 AND datepart(m, dbo.tblErrors.ErrorDate) = 6 Then 1 Else 0 End)as 'June',
SUM(Case when datepart(yyyy, dbo.tblErrors.ErrorDate) = 2006 AND datepart(m, dbo.tblErrors.ErrorDate) = 7 Then 1 Else 0 End)as 'July',
SUM(Case when datepart(yyyy, dbo.tblErrors.ErrorDate) = 2006 AND datepart(m, dbo.tblErrors.ErrorDate) = 8 Then 1 Else 0 End)as 'August',
SUM(Case when datepart(yyyy, dbo.tblErrors.ErrorDate) = 2006 AND datepart(m, dbo.tblErrors.ErrorDate) = 9 Then 1 Else 0 End)as 'September',
SUM(Case when datepart(yyyy, dbo.tblErrors.ErrorDate) = 2006 AND datepart(m, dbo.tblErrors.ErrorDate) = 10 Then 1 Else 0 End)as 'October',
SUM(Case when datepart(yyyy, dbo.tblErrors.ErrorDate) = 2006 AND datepart(m, dbo.tblErrors.ErrorDate) = 11 Then 1 Else 0 End)as 'November',
SUM(Case when datepart(yyyy, dbo.tblErrors.ErrorDate) = 2006 AND datepart(m, dbo.tblErrors.ErrorDate) = 12 Then 1 Else 0 End)as 'December'
FROM dbo.Main INNER JOIN
dbo.tblErrors ON dbo.Main.ID = dbo.tblErrors.ID
WHERE dbo.Main.UserName = @UserName
GROUP BY datepart(m, dbo.tblErrors.ErrorDate), datepart(yyyy, dbo.tblErrors.ErrorDate), dbo.Main.UserName
ORDER BY datepart(m, dbo.tblErrors.ErrorDate), datepart(yyyy, dbo.tblErrors.ErrorDate), dbo.Main.UserName

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-18 : 12:33:34

This?


SELECT		year(e.ErrorDate) AS 'Year',
		month(e.ErrorDate) AS 'Month',
		m.UserName,
		SUM(Case when month(e.ErrorDate) = 1 Then 1 Else 0 End) as 'January',
		SUM(Case when month(e.ErrorDate) = 2 Then 1 Else 0 End) as 'February',
		SUM(Case when month(e.ErrorDate) = 3 Then 1 Else 0 End) as 'March',
		SUM(Case when month(e.ErrorDate) = 4 Then 1 Else 0 End) as 'April',
		SUM(Case when month(e.ErrorDate) = 5 Then 1 Else 0 End) as 'May',
		SUM(Case when month(e.ErrorDate) = 6 Then 1 Else 0 End) as 'June',
		SUM(Case when month(e.ErrorDate) = 7 Then 1 Else 0 End) as 'July',
		SUM(Case when month(e.ErrorDate) = 8 Then 1 Else 0 End) as 'August',
		SUM(Case when month(e.ErrorDate) = 9 Then 1 Else 0 End) as 'September',
		SUM(Case when month(e.ErrorDate) = 10 Then 1 Else 0 End) as 'October',
		SUM(Case when month(e.ErrorDate) = 11 Then 1 Else 0 End) as 'November',
		SUM(Case when month(e.ErrorDate) = 12 Then 1 Else 0 End) as 'December'
FROM		dbo.Main as m
INNER JOIN	dbo.tblErrors as e ON e.ID = m.ID 
WHERE		m.UserName = @UserName
GROUP BY	month(e.ErrorDate),
		year(e.ErrorDate),
		m.UserName
ORDER BY	year(e.ErrorDate) desc,
		month(e.ErrorDate),
		m.UserName

Peter Larsson
Helsingborg, Sweden

JTProg
Starting Member

24 Posts

Posted - 2007-01-18 : 12:40:32

This works much better. Thanks for your help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-18 : 12:46:26

I think you should remove the month part from both the select statement, the group by statement and the order by statement.

SELECT		year(e.ErrorDate) AS 'Year',
		m.UserName,
		SUM(Case when month(e.ErrorDate) = 1 Then 1 Else 0 End) as 'January',
		SUM(Case when month(e.ErrorDate) = 2 Then 1 Else 0 End) as 'February',
		SUM(Case when month(e.ErrorDate) = 3 Then 1 Else 0 End) as 'March',
		SUM(Case when month(e.ErrorDate) = 4 Then 1 Else 0 End) as 'April',
		SUM(Case when month(e.ErrorDate) = 5 Then 1 Else 0 End) as 'May',
		SUM(Case when month(e.ErrorDate) = 6 Then 1 Else 0 End) as 'June',
		SUM(Case when month(e.ErrorDate) = 7 Then 1 Else 0 End) as 'July',
		SUM(Case when month(e.ErrorDate) = 8 Then 1 Else 0 End) as 'August',
		SUM(Case when month(e.ErrorDate) = 9 Then 1 Else 0 End) as 'September',
		SUM(Case when month(e.ErrorDate) = 10 Then 1 Else 0 End) as 'October',
		SUM(Case when month(e.ErrorDate) = 11 Then 1 Else 0 End) as 'November',
		SUM(Case when month(e.ErrorDate) = 12 Then 1 Else 0 End) as 'December'
FROM		dbo.Main as m
INNER JOIN	dbo.tblErrors as e ON e.ID = m.ID 
WHERE		m.UserName = @UserName
GROUP BY	year(e.ErrorDate),
		m.UserName
ORDER BY	year(e.ErrorDate) desc,
		m.UserName

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources