IF ELSE as part of a WHERE condition

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

General SQL Server Forums

New to SQL Server Programming

IF ELSE as part of a WHERE condition

Author

Topic

AdenJones
Starting Member

17 Posts

Posted - 2009-10-21 : 00:13:30

The following is a query where I need to have a dynamic AND condition
I haven't been able to find any examples of creating dynamic WHERE conditions using IF or CASE.
Any help would be greatly appreciated.

SELECT COUNT(*) AS Total, CONVERT(varchar(2),DATEPART(hh,tblWODTimeRange.TimeValue)) + ':' + CONVERT(varchar(2),DATEPART(mi,tblWODTimeRange.TimeValue)) AS TimeString
	FROM tblWODTimeRange CROSS JOIN tblAttendance
	WHERE tblAttendance.WODHours > 0
	AND tblAttendance.Deleted = 0
	AND DATEPART(hh,tblAttendance.StartTime) <= DATEPART(hh,tblWODTimeRange.TimeValue)
		IF (DATEPART(hh,tblAttendance.StartTime) = DATEPART(hh,tblWODTimeRange.TimeValue)) 
		BEGIN
		AND DATEPART(mi,tblAttendance.StartTime) <= DATEPART(mi,tblWODTimeRange.TimeValue)
		END
	AND DATEPART(hh,tblAttendance.EndTime) >= DATEPART(hh,tblWODTimeRange.TimeValue)
		IF (DATEPART(hh,tblAttendance.EndTime) = DATEPART(hh,tblWODTimeRange.TimeValue)) 
		BEGIN
		AND DATEPART(mi,tblAttendance.EndTime) >= DATEPART(mi,tblWODTimeRange.TimeValue) 
		END
	GROUP BY tblWODTimeRange.TimeValue,
	CONVERT(varchar(2),DATEPART(hh,tblWODTimeRange.TimeValue)) + ':' + CONVERT(varchar(2),DATEPART(mi,tblWODTimeRange.TimeValue))
	ORDER BY tblWODTimeRange.TimeValue ASC

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-10-21 : 00:43:36

You can use CASE in a WHERE clause.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

AdenJones
Starting Member

17 Posts

Posted - 2009-10-21 : 01:35:12

Then if you "can" use it, how do you use it?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-10-21 : 01:49:52

CASE WHEN Condition1 ... THEN ... ELSE ... END

It's hard to provide a concise answer for your question as we don't have a clear understanding of what you want.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-21 : 03:50:01

Have you read this?
www.sommarskog.se/dyn-search.html

Madhivanan

Failing to plan is Planning to fail

AdenJones
Starting Member

17 Posts

Posted - 2009-10-21 : 07:35:07

So you mean't that you can use CASE but you can't use IF?

AdenJones
Starting Member

17 Posts

Posted - 2009-10-21 : 07:37:26

[code]IF (DATEPART(hh,tblAttendance.StartTime) = DATEPART(hh,tblWODTimeRange.TimeValue))
BEGIN
AND DATEPART(mi,tblAttendance.StartTime) <= DATEPART(mi,tblWODTimeRange.TimeValue)
END[/code]

Is where Iam trying to have a conditional and statement I tried CASE as in the following

[code]CASE WHEN (DATEPART(hh,tblAttendance.StartTime) = DATEPART(hh,tblWODTimeRange.TimeValue))
THEN
AND DATEPART(mi,tblAttendance.StartTime) <= DATEPART(mi,tblWODTimeRange.TimeValue)
END[/code]

And it is still displaying an error message

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources