Stored Proc Help 1

[free47]

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!!

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

 

[bborissov]

UNION w/o ALL set only DISTINCT records in the result set.
So you maybe need UNION ALL instead of just UNION.


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[free47]

Thanks, i tried it both ways and it keeps giving me the same two errors:

Msg 8155, Level 16, State 2, Procedure rpt_summaryDataNew, Line 7
No column was specified for column 3 of 'source'.
Msg 207, Level 16, State 1, Procedure rpt_summaryDataNew, Line 29
Invalid column name 'topic'.

This is the updated proc

ALTER PROCEDURE [dbo].[rpt_summaryDataNew] 
(
@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, case when incident.customerEscalated=1 then 'Escalated' end
FROM         Incident 
where Incident.dateCreated BETWEEN @startDate and @endDate 
union all

SELECT     Incident.id, Incident.Division, History.action as topic
FROM         Incident INNER JOIN
                      History ON Incident.id = History.incidentId
where (action='reopened' /*or action='escalated'*/) and Incident.dateCreated BETWEEN @startDate and @endDate 
union all

SELECT     Incident.id, Incident.Division, Topic.name 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

 

[SQLSister]

Well Topic is only defined inthe second and third parts of the union but the derived table takes the field names form the first part of the union. That might fix the other error two as it can't create the derived table without a name for every column.

"NOTHING is more important in a database than integrity." ESquared

 

[free47]

Thanks, now it works perfectly! This wasn't an easy SP and i think i just got frustrated looking at it after a while...