I am trying to update this stored procedure that is using a pivot table, but for some reason I can’t get it to display like I need. It should add up when customer escalated = 1 but needs to count separate and not be in a case statement as it then doesn’t count the topic correctly. When I put it in a select statement by itself as it should be, it doesn’t work, maybe i am not doing it correctly. Any ideas are much appreciated!!
Code:
CREATE PROCEDURE [dbo].[rpt_summaryData]
(
@startDate datetime, @enddate datetime
)
AS
select division, [Escalated], [Reopened],[Service Issue], [Billing], [Information] , [New Service Request] as [New Request], [Other]
from
(
SELECT Incident.id, Incident.Division, History.action as topic
FROM Incident INNER JOIN
History ON Incident.id = History.incidentId
where (action='reopened') and Incident.dateCreated BETWEEN @startDate and @endDate
union
SELECT Incident.id, Incident.Division,
case when incident.customerEscalated=1 then 'Escalated' else Topic.name end AS topic
FROM Incident INNER JOIN
Topic ON Incident.topic = Topic.id
where Incident.dateCreated BETWEEN @startDate and @endDate
) as source
PIVOT
(
count(id)
FOR topic IN ([Escalated], [Reopened],[Service Issue], [Billing], [Information] , [New Service Request] , [Other] )
) as pvt
order by [Escalated] desc