Pivot Table help? Or some sort of summary

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)

Pivot Table help? Or some sort of summary

Author

Topic

mchute
Starting Member

4 Posts

Posted - 2008-11-11 : 07:23:53

I need to get a list of the TOP 10 categories of all time and then get month by month the total of those in each month

How can this be done??

I have spent since 9am on this already (now 12:30pm) and cannot find a way to do this

The query below gets the top 10 all time categories but I cant seem to find a way to get month by month

Please Help!!!

mchute
Starting Member

4 Posts

Posted - 2008-11-11 : 07:33:39

This was the query...

SELECT TOP 10 (Prob.ID),Prob.Name, count(*)
FROM tblServiceRequest
LEFT JOIN tblUser Usr2 on Usr2.id = tblServiceRequest.RequesterID
INNER JOIN tblProblemType prob on tblServiceRequest.ProblemType = prob.ID
WHERE Usr2.CompanyID in ( SELECT id FROM tblcompany WHERE id in (SELECT idCompany FROM tbl_GeographyCompany
WHERE idGeography = 1))
AND TimeLogged between '01-Jan-2008' and '30-Sep-2008'
GROUP BY prob.ID, Prob.Name
ORDER BY count(*) DESC

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-11 : 07:36:06

You haven't specified any sample data, But this should give you a clue.

select
MonthColumn,Total=sum(Qty)
from
(
select top 10 Monthcolumn,Qty from table order by [categorycolumn]
)t

hiteshj78
Starting Member

6 Posts

Posted - 2008-11-11 : 07:40:04

SELECT top 10 ROW_NUMBER OVER (ORDER BY Name) AS 'SNo',
t1.Name,
t1.Amount,
t2.Jan,t2.Feb,t2.Mar,....,t2.Dec
FROM Table t1
INNER JOIN
(SELECT Name,
SUM (CASE WHEN MONTH(Date) = 1 THEN Amount ELSE 0 END) AS 'Jan',
SUM (CASE WHEN MONTH(Date) = 2 THEN Amount ELSE 0 END) AS 'Feb',
SUM (CASE WHEN MONTH(Date) = 3 THEN Amount ELSE 0 END) AS 'Mar',
......
SUM (CASE WHEN MONTH(Date) = 12 THEN Amount ELSE 0 END) AS 'Dec'
FROM Table t
GROUP BY Name) t2
ON t1.Name=t2.Name

mchute
Starting Member

4 Posts

Posted - 2008-11-11 : 08:01:09

I cant seem to get that query working :(

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-11 : 09:20:55

SELECT DATENAME(month,t1.TimeLogged),count(t.ID)
FROM
(
SELECT
TOP 10 (Prob.ID),Prob.Name
FROM
tblServiceRequest
LEFT JOIN tblUser Usr2 on Usr2.id = tblServiceRequest.RequesterID
INNER JOIN tblProblemType prob on tblServiceRequest.ProblemType = prob.ID
WHERE
Usr2.CompanyID in ( SELECT id FROM tblcompany WHERE id in (SELECT idCompany FROM tbl_GeographyCompany WHERE idGeography = 1))
AND TimeLogged between '01-Jan-2008' and '30-Sep-2008'
GROUP BY
prob.ID, Prob.Name
ORDER BY
count(*) DESC
)t
JOIN tblServiceRequest t1 on t.ID=t1.ID
GROUP BY
DATENAME(month,t1.TimeLogged)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources