SQL Server 2000 - Case Statement and Processing speed

[cfcProgrammer]

Hi,

I am trying to create a report using asp/html calling a stored procedure to get the data.

One of the columns needed is gathered using this sql statement.

select distinct mn as "Month",s.name as "Name"
from flt f join mission m on f.mseq=m.mseq
join edt e on e.mseq = f.mseq
join species s on s.scicode = e.fish
where convert(datetime,(convert(varchar(2),f.mn) + '/' + convert(varchar(2), f.dy) + '/' + convert(varchar(4),f.yr)))
between '01-APR-2007' and '31-mar-2008' 
and left(m.mid,4)like 'BKS%'
GROUP BY mn,s.name
order by mn,s.name

and the result is

Month    Fishery 
1	CLAMS
1	CLAMS-STIMPSON SURF
1	GROUNDFISH-GEN
1	HALIBUT
1	LOBSTER
1	UNDETERMINED
1	SCALLOPS
2	CLAMS
2	GROUNDFISH-GEN
2	LOBSTER
2	SCALLOPS
2	UNDETERMINED
3	CLAMS
3	CLAMS-STIMPSON SURF
3	CRABS

What I need is a column for each month with all of the fishery types for that month in one field.
Example;

Month     Fishery
1	CLAMS,CLAMS-STIMPSON SURF,GROUNDFISH-GEN,HALIBUT,
	 LOBSTER,UNDETERMINED,SCALLOPS
2	CLAMS,GROUNDFISH-GEN,LOBSTER,SCALLOPS,UNDETERMINED
3	CLAMS,CLAMS-STIMPSON SURF,CRABS

Can this be done using a case statement or is there another way of accomplishing this?

Also, the query I displayed above takes about 37seconds to run, is there anything that you can suggest I change in order to speed this up a bit?

Any help that you can provide will most certainly be appreciated.

Thank you so much
Colleen

cfcProgrammer

 

[mrdenny]

With your where clause like it is, your performance won't be very good. There isn't going to be any way for SQL Server to use any indexes. I would recommend a table redesign to store the date in the mission column as a single field with a data type of datetime instead of three seperate columns.

As for converting the Fishery column into a comma separated list, you'll need to load your output into a function and use that to flatten the values into a single value. There are several examples on the forum on how to do this.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog