Case Statement with Or and And Conditions

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

SQL Server Development (2000)

Case Statement with Or and And Conditions

Author

Topic

vani_r14
Starting Member

24 Posts

Posted - 2009-01-14 : 23:13:41

Hi all

Currently I am trying to edit a bit of code that has a Case Statement and has both or and And conditions included in the When Clause.

declare @sdate datetime, @edate datetime
set @sdate = '2008-10-05 00:00:00.000'
set @edate = '2008-01-11 00:00:00.000'
CASE WHEN ((LEFT(t.Project_Short_Name,6) = 'PR') or
(LEFT(t.Project_Short_Name,5) like '[0-9]')
and t.period_end_date between @sdate and @edate)
THEN CONVERT(float,t.Hours) ELSE CONVERT(float, 0) END
AS ProjectHours

The code is picking up information from the entire table rather than just for the time period specified. Can some one please tell me why.

Thanks
vani

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-15 : 00:12:08

you missed an extra braces


declare @sdate datetime, @edate datetime
set @sdate = '2008-10-05 00:00:00.000'
set @edate = '2008-01-11 00:00:00.000'
CASE WHEN ((LEFT(t.Project_Short_Name,6) = 'PR') or 
(LEFT(t.Project_Short_Name,5) like '[0-9]')) 
and t.period_end_date between @sdate and @edate)
THEN CONVERT(float,t.Hours) ELSE CONVERT(float, 0) END 
AS ProjectHours

vani_r14
Starting Member

24 Posts

Posted - 2009-01-15 : 01:49:44

Hi

Thanks for that.. But that is still not working. However I noticed that if i put the date period condition in the where clause that is working for the right periods but not when it is in the Case Statement.

I tried just a simple select with this case and no where clause then I get data from the entire table. please help

Thanks
vani

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-01-15 : 02:24:13

Try

declare @sdate datetime, @edate datetime
set @sdate = '2008-10-05 00:00:00.000'
set @edate = '2008-01-11 00:00:00.000'
CASE WHEN ((LEFT(t.Project_Short_Name,6) = 'PR') or
(LEFT(t.Project_Short_Name,5) like '[0-9]'))
and t.period_end_date >=@sdate and t.period_end_date <dateadd(day,1,@edate)
THEN CONVERT(float,t.Hours) ELSE CONVERT(float, 0) END
AS ProjectHours

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-15 : 07:48:46

quote:
Originally posted by vani_r14

Hi

Thanks for that.. But that is still not working. However I noticed that if i put the date period condition in the where clause that is working for the right periods but not when it is in the Case Statement.

I tried just a simple select with this case and no where clause then I get data from the entire table. please help

Thanks
vani

then explain what output you're looking at with some sample data

vani_r14
Starting Member

24 Posts

Posted - 2009-01-28 : 16:18:06

Hi all

Thank you for your help.. Its working now

Thanks again

vani

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources