Help with a Stored Procedure - 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)

Help with a Stored Procedure

Author

Topic

mitchelt
Starting Member

16 Posts

Posted - 2006-02-15 : 12:24:12

Hi,

I currently have a report (ASP/VB SCRIPT) that queries our SQL 2000 DB and brings back a distinct list of classes which were taught and also the number of times it was taught per quarter. I am currently using multiple recordsets and the processing is done on the page so you can image that a report that has to go through over 5000 records can take a while because of all of the "roundtrips" to the SQL Server.

What I am currently doing is using one recordset to get all of the distinct class codes and setting up repeat region, then I pass that value to four different recordsets sets (winter, spring, summer, fall) and they count the times the class was taught as a nested repeat region/loop, then it goes to the next class and performs the same counting function.

I know this is not very efficient but I cannot figure out how to code a stored procedure to do this.

Attached is an image that will help illustrate what I am trying to do.

Thanks for taking a look,

Mitch

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-02-15 : 12:59:25

How about...

select
classcode,
isnull(case [quarter] when 'winter' then count(*) end, 0) as 'Count Winter',
isnull(case [quarter] when 'spring' then count(*) end, 0) as 'Count Spring',
isnull(case [quarter] when 'summer' then count(*) end, 0) as 'Count Summer',
isnull(case [quarter] when 'fall' then count(*) end, 0) as 'Count Fall'
from classtaught
group by classcode, [quarter]

Edit: That isn't quite going to work.

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-02-15 : 13:10:40

Try this instead...

declare @classtaught table (classcode varchar(30), wintercount int, springcount int, summercount int, fallcount int)
insert into @classtaught
select
classcode,
isnull(case [quarter] when 'winter' then count(*) end, 0) as 'Count Winter',
isnull(case [quarter] when 'spring' then count(*) end, 0) as 'Count Spring',
isnull(case [quarter] when 'summer' then count(*) end, 0) as 'Count Summer',
isnull(case [quarter] when 'fall' then count(*) end, 0) as 'Count Fall'
from classtaught
group by classcode, [quarter]
order by classcode

select classcode, sum(wintercount), sum(springcount), sum(summercount), sum(fallcount)
from @classtaught
group by classcode
order by classcode

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-02-15 : 13:13:10

Try the combination of the following 2

Select Classcode, Quarter, Count(*) from Classtaught Group By Classcode, Quarter

&

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56058

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-15 : 17:24:31

[code]select classcode,
sum(case [quarter] when 'winter' then 1 else 0 end) as [Count Winter],
sum(case [quarter] when 'spring' then 1 else 0 end) as [Count Spring],
sum(case [quarter] when 'summer' then 1 else 0 end) as [Count Summer],
sum(case [quarter] when 'fall' then 1 else 0 end) as [Count Fall]
from classtaught
group by classcode[/code]

----------------------------------
'KH'
everything that has a beginning has an end

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-16 : 01:36:35

Also refer
http://weblogs.sqlteam.com/jeffs/archive/2005/05.aspx

Madhivanan

Failing to plan is Planning to fail

mitchelt
Starting Member

16 Posts

Posted - 2006-02-16 : 16:47:15

Thank you everyone for the fabulous help!!!

I was able to use the examples you gave me and the report which used to take 40 seconds to run and generate an Excel file is now taking 4 seconds!!!

Once again...thanks!

Mitch

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources