where case - SQL Server Forums

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)

where case

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-02-10 : 06:30:30

Hello,
Is it possible to have a case statement inside a where clause?
I get an error on the linie inidicated below. Do yo usee why please?
Thanks

select
...
...
...
from
@tblMain
where
Active = 1
and
--(f.ProcessDate >= @FirstDayOfQuarter OR f.ProcessDate <= @LastDayOfQuarter)
case
when
(
f.AccountID = @AccountAssetID
OR
f.AccountID = @AccountLiabilityID
)
then
(
f.ProcessDate >= @LastDayOfPreviousQuarter -- ERROR. incorrect syntax neat '>'
AND
f.ProcessDate <= @LastDayOfQuarter
)
end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 06:39:01

it should be


....
where	
Active = 1
and 
((
f.ProcessDate >= @LastDayOfPreviousQuarter 
AND 
f.ProcessDate <= @LastDayOfQuarter
)
OR
(
f.AccountID <> @AccountAssetID
AND
f.AccountID <> @AccountLiabilityID
))

------------------------------------------------------------------------------------------------------
SQL Server MVP

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-02-10 : 06:50:56

[code]select
...
...
...
from
@tblMain
Where Active = 1
and
f.AccountID in (@AccountAssetID, @AccountLiabilityID)
and
(f.ProcessDate between @LastDayOfPreviousQuarter and @LastDayOfQuarter)[/code]

Harsh Athalye
http://www.letsgeek.net/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-02-10 : 06:52:54

This is not correct.
The following senario I believe will help you understand what I am trying to do in the WHERE clause...

Basically,
if AccountID is NOT AssetID or LiabilityID then make sure you are looking for the ProcessDate which is between lastdayofPreviousquarter and lastdayofquarter

if AccountID is AssetID or LiabilityID then make sure you are looking for the ProcessDate which is between firstdayofquarter and lastdayofquarter

if (f.AccountID is NOT @AccountAssetID or @AccountLiabilityID)
then (f.ProcessDate >= @FirstDayOfQuarter AND f.ProcessDate <= @LastDayOfQuarter )

AND
if (f.AccountID is @AccountAssetID or @AccountLiabilityID)
then (f.ProcessDate >= @LastDayOfPreviousQuarter AND f.ProcessDate <= @LastDayOfQuarter )

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-02-10 : 06:58:02

[code]
Where Active = 1
and
(
f.AccountID in (@AccountAssetID, @AccountLiabilityID)
and
(f.ProcessDate between @LastDayOfPreviousQuarter and @LastDayOfQuarter))
OR
(
f.AccountID NOT IN (@AccountAssetID, @AccountLiabilityID)
and
(f.ProcessDate between @FirstDayOfQuarter and @LastDayOfQuarter))
[/code]

Harsh Athalye
http://www.letsgeek.net/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-02-10 : 07:05:49

Once again, thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources