How to find the REST of a TOP 5

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)

How to find the REST of a TOP 5

Author

Topic

Makaio780
Starting Member

24 Posts

Posted - 2012-07-13 : 17:23:46

How would i find the sum of the rest of records as 'Other'

SELECT TOP(5) COUNT(vARS76_HPD_Help_Desk.Incident_Number) AS Expr1
,vARS76_HPD_Help_Desk.Incident_Type AS ITYPE
,vARS76_HPD_Help_Desk.Product_Name AS PName
FROM vARS76_HPD_Help_Desk RIGHT OUTER JOIN
vTbl_ServiceDeskAnalystList
ON vARS76_HPD_Help_Desk.Submitter = vTbl_ServiceDeskAnalystList.UID
WHERE (vARS76_HPD_Help_Desk.Reported_Date > '6/1/2012')
AND (vARS76_HPD_Help_Desk.Reported_Date < '7/1/2012')
AND (vARS76_HPD_Help_Desk.Owner_Group = 'HD-Edmonton')
AND (vARS76_HPD_Help_Desk.Incident_Type = 'User Service Restoration')
AND (vARS76_HPD_Help_Desk.Product_Name <> '')
AND (DATEPART(Hour, vARS76_HPD_Help_Desk.Reported_Date) BETWEEN 5 AND 22
AND DATEPART(Weekday, vARS76_HPD_Help_Desk.Reported_Date) BETWEEN 2 AND 6
OR DATEPART(Hour, vARS76_HPD_Help_Desk.Reported_Date) BETWEEN 7 AND 16
AND DATEPART(Weekday, vARS76_HPD_Help_Desk.Reported_Date) IN (1, 7))
GROUP BY vARS76_HPD_Help_Desk.Incident_Type,vARS76_HPD_Help_Desk.Product_Name
ORDER BY COUNT(vARS76_HPD_Help_Desk.Incident_Number) DESC

Thanks in advance =D

Ron Cheung

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-07-13 : 18:21:12

[code];WITH cteSource(Yak, Expr1, ITYPE, PName)
AS (
SELECT ROW_NUMBER() OVER (ORDER BY COUNT(hd.Incident_Number) DESC) AS Yak,
COUNT(hd.Incident_Number) AS Expr1,
hd.Incident_Type AS ITYPE,
hd.Product_Name AS PName
FROM vARS76_HPD_Help_Desk AS hd
RIGHT JOIN vTbl_ServiceDeskAnalystList AS al ON hd.Submitter = al.UID
WHERE hd.Reported_Date >= '20120601'
AND hd.Reported_Date < '20120701'
AND hd.Owner_Group = 'HD-Edmonton'
AND hd.Incident_Type = 'User Service Restoration'
AND hd.Product_Name > ''
AND (
DATEPART(HOUR, hd.Reported_Date) BETWEEN 5 AND 22
AND DATEPART(WEEKDAY, hd.Reported_Date) BETWEEN 2 AND 6
OR DATEPART(HOUR, hd.Reported_Date) BETWEEN 7 AND 16
AND DATEPART(WEEKDAY, hd.Reported_Date) IN (1, 7)
)
GROUP BY hd.Incident_Type,
hd.Product_Name
)
SELECT SUM(Expr1) AS Expr1,
CASE WHEN Yak BETWEEN 1 AND 5 THEN MIN(ITYPE) ELSE 'Other' END AS ITYPE,
CASE WHEN Yak BETWEEN 1 AND 5 THEN MIN(PName) ELSE 'Other' END AS PName
FROM cteSource
GROUP BY CASE
WHEN Yak BETWEEN 1 AND 5 THEN Yak
ELSE 6
END
ORDER BY MIN(Yak)[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources