Search proc and WHERE problem...

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

Transact-SQL (2005)

Search proc and WHERE problem...

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2009-08-21 : 09:35:18

Ok, user has 3 choices to search.
1, 2, or all 3 can be used.

Need to add another criteria in the proc.
For instance, if only clientID is chosen,
Also need to include those not only completestatus = 1
But only for that client, processDate >= md.Days but only for that client....

	FROM ImportAlert a
	INNER JOIN ImportType b ON a.ImportTypeID = b.ImportTypeID
	INNER JOIN Client c ON a.ClientID = c.pk_id
	INNER JOIN ImportMaxDays md ON a.ImportTypeID = md.ImportTypeID

	WHERE	
		(
			(@clientID IS NULL OR @clientID = '' OR (a.ClientID = @clientID OR DATEDIFF(day, a.ProcessDate, GETDATE()) >= md.Days))
			AND (@type IS NULL OR @type = '' OR (a.ImportTypeID = @type OR DATEDIFF(day, a.ProcessDate, GETDATE()) >= md.Days))
			AND (@days IS NULL OR @days = '' OR DATEDIFF(day, a.CompletedDate, GETDATE()) <= @days)
		)	
		AND 
	a.CompleteStatus = 1  --AND DATEDIFF(day, a.ProcessDate, GETDATE()) >= md.Days)

Right now how it is isn't working right.
It brings back more than just the one chose client.
@Type and @Days were null

Tried changing this a.ClientID = @clientID AND DATEDIFF(day, a.ProcessDate, GETDATE()) >= md.Days
With OR but then no rows returned.

Suggestions?

Thanks,

Zath

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2009-08-21 : 10:10:59

Ok, here's an update.
Was able to get the first 2 working but as soon as I add the days
with another pram, too many come back.
Such as client and days.
All clients come back but only need the one choses...

	WHERE	
		(
			(@clientID IS NULL OR @clientID = '' 
					OR (a.ClientID = @clientID AND DATEDIFF(day, a.ProcessDate, GETDATE()) <= md.Days 
						AND a.CompleteStatus = 1 )
					OR (a.ClientID = @clientID AND DATEDIFF(day, a.ProcessDate, GETDATE()) >= md.Days 
						AND (a.CompleteStatus = 1 OR a.CompleteStatus = 0)))
			AND (@type IS NULL OR @type = '' 
					OR (a.ImportTypeID = @type AND DATEDIFF(day, a.ProcessDate, GETDATE()) >= md.Days)
						AND DATEDIFF(day, a.ProcessDate, GETDATE()) <= md.Days 
						AND a.CompleteStatus = 1)
					OR (a.ImportTypeID = @type AND DATEDIFF(day, a.ProcessDate, GETDATE()) >= md.Days 
						AND (a.CompleteStatus = 1 OR a.CompleteStatus = 0))
			AND (@days IS NULL OR @days = '' 
					OR DATEDIFF(day, a.CompletedDate, GETDATE()) <= @days 
						AND a.CompleteStatus = 1 )
					OR (DATEDIFF(day, a.ProcessDate, GETDATE()) >= md.Days 
						AND (a.CompleteStatus = 1 OR a.CompleteStatus = 0))
		)	
		--AND a.CompleteStatus = 1  --AND DATEDIFF(day, a.ProcessDate, GETDATE()) >= md.Days)

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2009-08-21 : 10:53:52

Ok, got it working.
But there must be a better way to do this...

	WHERE	
		(
			(
				@clientID IS NULL OR @clientID = '' 
					OR ((a.ClientID = @clientID AND DATEDIFF(day, a.ProcessDate, GETDATE()) <= md.Days 
						AND a.CompleteStatus = 1 )
					OR (a.ClientID = @clientID AND DATEDIFF(day, a.ProcessDate, GETDATE()) >= md.Days) 
					AND (@type IS NULL OR @type = '' OR a.ImportTypeID = @type)
				)
			)
			AND (
					@type IS NULL OR @type = '' 
					OR ((a.ImportTypeID = @type AND DATEDIFF(day, a.ProcessDate, GETDATE()) >= md.Days)
						AND DATEDIFF(day, a.ProcessDate, GETDATE()) <= md.Days 
						AND a.CompleteStatus = 1)
					OR (a.ImportTypeID = @type AND DATEDIFF(day, a.ProcessDate, GETDATE()) >= md.Days 
						AND (@clientID IS NULL OR @clientID = '' OR a.ClientID = @clientID)
						)
				)

			AND (
					@days IS NULL OR @days = '' 
					OR (DATEDIFF(day, a.CompletedDate, GETDATE()) <= @days AND a.CompleteStatus = 1 )
					OR (DATEDIFF(day, a.ProcessDate, GETDATE()) >= md.Days 
						AND DATEDIFF(day, a.ProcessDate, GETDATE()) <= @days
						AND a.CompleteStatus = 0)
				)
		)	

	ORDER BY a.CompletedDate

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-08-22 : 01:34:49

quote:
Originally posted by Zath

Ok, got it working.
But there must be a better way to do this...

I don't see why. You have 3 different conditions so you need 3 different checks.

It does look like you might be able to factor out the status check but you haven't told us what you are doing so I won't risk a guess.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources