Thank you somabk2007 & Lynn. Both solutions work perfect. 😀

Thank you for your advice Celko, much appreciated. I will definately read up on ISO-8601 and ISO-11179.

Your data element names are insane. Take deep breath and think about...

LutzM (7/1/2011)

---

You could change the query to to

AND psf.assetRegion_ID >= @assetregion_id_min

AND psf.assetRegion_ID <= @assetregion_id_max

set @assetregion_id_min to 1, 2 or @assetRegion_ID and

@assetregion_id_max to 2147483647 (max value for an INT data...

Thanks for you replys.

LutzM - That sounds like it might just work I will give it a go now and see.

Dixie - Unfortunately I cannot use Dynamic Sql...

Thanks for all your excellent advice!

I will consider all options and see which works best in my situation.

Thanks

Darren

Thanks,

Speed is not an issue.

The problem I have is when I run the query these line's do not work properly

AND            pbas.adviser_type in (isnull(@new_adviser_type,pbas.adviser_type))

AND            pbas.country in (isnull(@new_country,pbas.country))

the...

Thanks for your response RGR'us.

I need to create a procedure to cover these search options on the application. 

company_name, services_offered, adviser_type, country

any one, or all of...

David Thanks for that,

I have also tried that approach, but as I have a few list strings passed in,  I would have to do lots of 'IF' statements to...

Thanks John,

I was hoping to avoid dynamic sql, but if there is no other way i will try that.

Thanks

Darren 

sorry that should read...

SELECT @start_date = max([time]) FROM table1 WHERE name = @name AND event = 'start'

SELECT @stop_date = max([time]) FROM table1 WHERE name = @name AND event = 'stop'

Darren

You could try this...

DECLARE @start_date AS DATETIME

DECLARE @stop_date AS DATETIME

DECLARE @name AS VARCHAR(10)

SET @name='Name1'

SELECT @start_date = [time] FROM table1 WHERE name = @name AND event = 'start'

SELECT @stop_date = [time] FROM table1 WHERE name = @name AND event = 'stop'

SELECT DATEDIFF(mi, @stop_date, @start_date) --...

Thanks RGR'us, Deepak!

I had tried the string concatenation but it was a bit messy and I couldn't quite get it to work!

RGR'us, that solution is perfect.

Thanks

Darren

Thanks for your responses.  I'll give them a try.