Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Stored Proc Help 1

Status
Not open for further replies.

free47

Programmer
Jun 9, 2008
10
US
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
 
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
 
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
Code:
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
 
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
 
Thanks, now it works perfectly! This wasn't an easy SP and i think i just got frustrated looking at it after a while...
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top