How to subtract Top5TotalRequest From TotalRequest

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 subtract Top5TotalRequest From TotalRequest

Author

Topic

Makaio780
Starting Member

24 Posts

Posted - 2012-07-24 : 17:38:44

Select Count(Incident_Number) as Total,'TotalRequest' as Request
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 (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)) and Incident_type = 'User Service Request'

UNION ALL

Select Sum(Top5Count) as Total,'Top5TotalRequest' as Request
From(
Select Top(5)Count(Incident_Number) as Top5Count
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 (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)) and Product_Name <> '' and Incident_type = 'User Service Request'
Group by Product_Name
Order by Count(Incident_Number) DESC
)as b

Ron Cheung

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-24 : 19:28:19

You can assign each query result to a variable and subtract. Or, you can make them subqueries and subtract, like this:

SELECT
	x.Total-y.Total
FROM
(
   -- Your First Query here
) as x
CROSS JOIN
(
   -- Your Second Query here
) as y

You can also do something fancier like this - may (or may not) be more efficient, if that is a consideration.

SELECT SUM(INcidentCountForProduct) -
       SUM(CASE WHEN RN <= 5 THEN INcidentCountForProduct ELSE 0 END) AS 
       TotalRequestMinusTop5Requests
FROM   (
           SELECT *,
                  ROW_NUMBER() OVER(ORDER BY INcidentCountForProduct DESC) AS RN
           FROM   (
                      SELECT ProductName,
                             COUNT(Incident_Number) AS INcidentCountForProduct
                      FROM   YourTable
                      GROUP BY
                             ProductName
                  )s1
       )s2

Makaio780
Starting Member

24 Posts

Posted - 2012-07-25 : 09:17:27

Thank you very much Sunitabeck!!!!!

Ron Cheung

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-25 : 10:44:43

Very welcome!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources