Stored procedure questions.

[longmatch]

I am writing this cross-tab query to generate a report.

SELECT category, procedurename, count(procedurename) AS No,
SUM(CASE involvement WHEN 'A' THEN 1 ELSE 0 END) AS A,
SUM(CASE involvement WHEN 'S' THEN 1 ELSE 0 END) AS S,
SUM(CASE involvement WHEN 'P' THEN 1 ELSE 0 END) AS P,
SUM(CASE involvement WHEN 'I' THEN 1 ELSE 0 END) AS I
FROM procedure_web
GROUP BY category, procedurename
ORDER BY category, procedurename

What I need to do is to generate a sum A, S, P, I for each category, in order to show a column with the total for each category. The category consists of many procedures.
I would like my report to look like
Category1, NoOfProcedureInthisCategory1
procedurename1, procedureCount, A, P, S, I
procedurename2, procedureCount, A, P, S, I
..........................................
Category2, NoOfProcedureInthisCategory2
procedurename3, procedureCount, A, P, S, I
procedurename4, procedureCount, A, P, S, I
...........................................
......................................
...........................................
...........................................


thanks

longmatch

 

[bertrandkis]

If I Understand you correctly you want to have on your report a group header showing:
Category1, NoOfProcedureInthisCategory1
and under this group hearder have the info about the stored procedure on the Category like this:

procedurename1, procedureCount, A, P, S, I
procedurename2, procedureCount, A, P, S, I
..........................................

I do not know what reporting tool you are using but this technique will give you a good output
Declare @tempcat table(Ctr int Identity,Category varchar(50),proccount int)
Create table #temp (category varchar(50),Proccount int,
procName varchar(50),Nos int,A int,S int,P int,I int)

Insert into @tempCat(Category,ProcCount)
SELECT category,count(procedurename)
FROM yourTable
Group by Category

--Second step
declare @counter int
set @counter=1
While @counter<=(select max(ctr) from @tempcat)
begin
Insert into #temp (Category,ProcCount)
Select Category,ProcCount from @tempcat where ctr=@counter
Insert into #temp (procName,Nos,A,S,P,I)
SELECT procedurename, count(procedurename) AS Nos,
SUM(CASE involvement WHEN 'A' THEN 1 ELSE 0 END) AS A,
SUM(CASE involvement WHEN 'S' THEN 1 ELSE 0 END) AS S,
SUM(CASE involvement WHEN 'P' THEN 1 ELSE 0 END) AS P,
SUM(CASE involvement WHEN 'I' THEN 1 ELSE 0 END) AS I
FROM procedure_web
Where Category=(Select category from @tempcat where ctr=@counter)
GROUP BY procedurename,involvement
ORDER BY procedurename,involvement
set @counter=@counter+1
end
Select * from #temp

I truly hope that this will help you and may be earn me an expert tip vote which I will greatly appreciate.
Regards
BertrandKis @blackbelt sequelist