Please help with complex search query

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

Transact-SQL (2000)

Please help with complex search query

Author

Topic

beady
Starting Member

28 Posts

Posted - 2006-07-03 : 06:30:26

I have developed (with the help of an article on this site) a stored procedure to accept one or more search parameters. Two of the parameters are dates. It works fine with one date, but I don't know how to retrieve all records in between two entered dates.

The procedure so far is:
CREATE Procedure Report_ListCases
@idCOURT integer = Null,
@citation_date1 datetime = Null,
@citation_date2 datetime = Null

AS
SELECT dbo.CASE_ENTITY.citation_date, CASE_ENTITY.idCOURT, CASE_ENTITY.case_name

FROM dbo.CASE_ENTITY

WHERE dbo.CASE_ENTITY.idCOURT = COALESCE (@idCOURT,idCOURT) AND
dbo.CASE_ENTITY.citation_date = COALESCE(@citation_date1,citation_date) AND
dbo.CASE_ENTITY.citation_date = COALESCE(@citation_date2,citation_date)
GO

Any help greatly appreciated!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-03 : 06:34:42

Try this

SELECT	dbo.CASE_ENTITY.citation_date,
	CASE_ENTITY.idCOURT,
	CASE_ENTITY.case_name
FROM	dbo.CASE_ENTITY 
WHERE	dbo.CASE_ENTITY.idCOURT = COALESCE (@idCOURT, idCOURT)
	AND dbo.CASE_ENTITY.citation_date = COALESCE(@citation_date1, @citation_date2, citation_date)

Peter Larsson
Helsingborg, Sweden

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-03 : 06:39:28

Or maybe this...

...
WHERE (idCOURT = @idCOURT AND NOT @idCOURT IS NULL)
  AND (citation_date >= @citation_date1 AND NOT @citation_date1 IS NULL)
  AND (citation_date <= @citation_date2 AND NOT @citation_date2 IS NULL)

And here's a good article on this...
http://www.sommarskog.se/dyn-search.html

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

beady
Starting Member

28 Posts

Posted - 2006-07-03 : 06:44:35

Thank you, Ryan, your solution worked just fine.

Peter, yours only retrieved the first two records of the first date parameter...not sure why/how that happened.

beady
Starting Member

28 Posts

Posted - 2006-07-03 : 06:49:10

The only problem with your solution, Ryan, is that I HAVE to enter both dates. It will accept just one date parameter, but it returns nothing at all, when it really should.

beady
Starting Member

28 Posts

Posted - 2006-07-03 : 06:56:26

Not to worry, I can work around it, Ryan. Thank you very much for your help

manohar
Starting Member

6 Posts

Posted - 2006-07-03 : 07:21:41

CREATE Procedure Report_ListCases
@idCOURT integer = Null,
@citation_date1 datetime = Null,
@citation_date2 datetime = Null

begin

AS
SELECT dbo.CASE_ENTITY.citation_date, CASE_ENTITY.idCOURT, CASE_ENTITY.case_name

FROM dbo.CASE_ENTITY

WHERE dbo.CASE_ENTITY.idCOURT = COALESCE (@idCOURT,idCOURT) AND
CONVERT(DATETIME,dbo.CASE_ENTITY.citation_date,101) between
convert(datetime,convert(varchar(10),@citation_date1,101) +' '+ '00:00:00.000 ',101) and
convert(datetime,convert(varchar(10),@citation_date2,101) +' '+ ' 23:59:59.997 ',101)

beady
Starting Member

28 Posts

Posted - 2006-07-03 : 07:47:23

Thank you, manhohar, perfect!

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-03 : 08:10:04

quote:
Originally posted by beady

The only problem with your solution, Ryan, is that I HAVE to enter both dates. It will accept just one date parameter, but it returns nothing at all, when it really should.

My bad. I got 2 methods crossed. Try this instead...

...
WHERE (idCOURT = @idCOURT OR @idCOURT IS NULL)
AND (citation_date >= @citation_date1 OR @citation_date1 IS NULL)
AND (citation_date <= @citation_date2 OR @citation_date2 IS NULL)

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-03 : 08:12:13

manohar/beady - There should be no need to use varchar when manipulating/comparing dates. This will be less efficient than using date functions and direct date comparisons.

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-03 : 08:22:48

I agree. Your SP header should look like this

CREATE Procedure Report_ListCases
@idCOURT integer = Null,
@citation_date1 datetime = 'January 1, 1753',
@citation_date2 datetime = 'December 31, 9999'

to avoid using nulls. Then call the SP with
Report_ListCases 2, default, default

if you want all dates. In the SP, write an ordinary DATE BETWEEN @citation_date1 AND @citation_date2.

Peter Larsson
Helsingborg, Sweden

beady
Starting Member

28 Posts

Posted - 2006-07-04 : 04:54:01

Thank you, guys, I have taken aboard all good advice and it's working just fine now. I'm a happy lassie now

... until my next glitchy encounter with sql.

beady
Starting Member

28 Posts

Posted - 2006-07-04 : 06:28:46

Well, well, well. It didn't take me long to be stumped again (my sql really is rusty).

While the dates of the SP is working just fine, how do I retrieve all records within one or more dates, but also with one or more idCourt parameters being set?

I wonder if this is even possible to achieve in the same SP...?

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-04 : 06:40:13

Now might be a good time to provide some example data, and the results you would like for that data...

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

beady
Starting Member

28 Posts

Posted - 2006-07-04 : 07:02:29

quote:
Originally posted by RyanRandall

Now might be a good time to provide some example data, and the results you would like for that data...

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

OK, here it goes:
for the same search,
idCOURT: a user chooses one or more court id's (integers)
citation_date: choose none, one or more dates (which we've got working now).

To summarize, so far we have the following SP with the date selections working, but I now need to have one or more idCOURT parameters selected. At this point it can only take one idCOURT parameter, not multiple. I need up to three idCOURT parameters. There is always going to be at least one idCOURT parameter:

CREATE Procedure Report_ListCases
@idCOURT integer = Null,
@citation_date1 datetime = 'January 1, 1753',
@citation_date2 datetime = 'December 31, 9999'

AS
SELECT CASE_ENTITY.citation_date, CASE_ENTITY.idCOURT, CASE_ENTITY.case_name, COURT.court_name

FROM CASE_ENTITY , COURT
WHERE (CASE_ENTITY.idCOURT = @idCOURT AND NOT @idCOURT IS NULL)
AND (COURT.idCOURT = CASE_ENTITY.idCOURT )
AND (citation_date >= @citation_date1 AND NOT @citation_date1 IS NULL)
AND (citation_date <= @citation_date2 AND NOT @citation_date2 IS NULL)

Is it possible to do what I want to do?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-04 : 07:11:56

Maybe something like this to avoid csv parameter

CREATE Procedure Report_ListCases
(
	@idCOURT1 integer = Null,
	@idCOURT2 integer = Null,
	@idCOURT3 integer = Null,
	@citation_date1 datetime = 'January 1, 1753',
	@citation_date2 datetime = 'December 31, 9999'
)
AS

SELECT		ce.citation_date,
		ce.idCOURT,
		ce.case_name,
		co.court_name
FROM		CASE_ENTITY ce
INNER JOIN	COURT co ON co.idCOURT = ce.idCOURT
WHERE		((ce.idCOURT = @idCOURT1 OR ce.idCOURT = @idCOURT2 OR ce.idCOURT = @idCOURT3)
			OR (@idCOURT1 IS NULL AND @idCOURT2 IS NULL AND @idCOURT3 IS NULL))
		AND citation_date BETWEEN @citation_date1 AND @citation_date2

Call with EXEC Report_ListCases default, 2, null, '2007-01-01', default

Peter Larsson
Helsingborg, Sweden

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-04 : 07:17:59

Or, if you choose to pass a csv parameter instead, these articles might come in handy...

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
http://www.sql-server-performance.com/mm_list_random_values.asp
http://www.sommarskog.se/arrays-in-sql.html

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-04 : 07:50:41

quote:
Originally posted by beady

I need up to three idCOURT parameters. There is always going to be at least one idCOURT parameter:

CREATE Procedure Report_ListCases
(
	@idCOURT1 integer = Null,
	@idCOURT2 integer = Null,
	@idCOURT3 integer = Null,
	@citation_date1 datetime = 'January 1, 1753',
	@citation_date2 datetime = 'December 31, 9999'
)
AS

SELECT		ce.citation_date,
		ce.idCOURT,
		ce.case_name,
		co.court_name
FROM		CASE_ENTITY ce
INNER JOIN	COURT co ON co.idCOURT = ce.idCOURT
WHERE		(ce.idCOURT = @idCOURT1 OR ce.idCOURT = @idCOURT2 OR ce.idCOURT = @idCOURT3)
		AND citation_date BETWEEN @citation_date1 AND @citation_date2

Call with EXEC Report_ListCases 19, default, null, '2007-01-01', default

Peter Larsson
Helsingborg, Sweden

beady
Starting Member

28 Posts

Posted - 2006-07-04 : 07:52:59

I have to avoid passing csv parameter so Peter's solution wins

Thanks a million to Peter and Ryan!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources