One script vs two - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

One script vs two

Author

Topic

handora
Starting Member

2 Posts

Posted - 2006-04-19 : 11:19:06

Hello,

Using SQL Server 2000 Query Analyzer, I am trying to display something like the below example with a report

CategoryCode = Q Submissions = 20
340 10
320 5
330 4
ODO 1
CategoryCode = B Submissions = 15
620 8
640 4
330 2
ODO 1

The codes under the CategoryCode refers to DescriptionCodes and numbers the total number of submissions for those DescriptionCodes under the specific CategoryCode.

I have no problem to get the results in two separate stored procedures, considering that CategoryCode is passed to display the DescriptionCodes.

Can it be done in one script in the same format without using a cursor?
Thanks for your time,
Handora

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-19 : 11:24:55

Can u provide with ur table(s) structure, some sample data and expected results?

Its hard to find
DescriptionCodes ?
Submissions ?
The lists of data (that u have given)?

One thing is sure, that u'd do it without a cursor

Srinika

handora
Starting Member

2 Posts

Posted - 2006-04-19 : 11:49:45

I know. I am sorry for sending not enough info. There are a few tables involved and the report is more complex than I am showing. I tried to eliminate all and just give you my main problem.
OK. I'll try this way. Let's say I have the following table with the test data below.

select TicketID, SeverityCode, CategoryCode, DescriptionCode
from Tickets

1 S B 010
2 2 H 610
3 3 N 840
10 S B 010
11 S B 010
12 S B 010
13 S B 010
50 3 H 610
51 3 H 610
59 S B 010
61 S B 010
62 S B 010
63 S B 610
64 S B 010
65 1 B 610

when I try display the first part of the report, I would need a query like below:
SELECT CategoryCode, count(CategoryCode) as 'Submissions'
FROM Tickets
GROUP BY CategoryCode
ORDER BY Submissions desc

and will get the results
CategoryCode Submissions
B 11
H 3
N 1

for the second part I'll need something as the following:
DECLARE @CategoryCode varchar(10)
select @CategoryCode = 'B'
SELECT DescriptionCode, count(DescriptionCode) as 'Submissions'
FROM Tickets
WHERE CategoryCode = @CategoryCode
GROUP BY DescriptionCode
ORDER BY Submissions desc

and I'll get the results
DescriptionCode Submissions
010 9
610 2

I want to accomplish in one shot to display
CategoryCode = B Submissions = 11
010 9
610 2
CategoryCode = H Submissions = 3
610 3
CategoryCode = N Submissions = 1
840 1

Is it possible?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources