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)

case

Author

Topic

cjhardie
Yak Posting Veteran

58 Posts

Posted - 2007-03-09 : 14:09:30

I have a where statement and I need to change part of it to be 1 if it is 0. so i tried this:

where
thedate between sf.startdate and sf.enddate
and da.deletedate is null
and da.siteregionvenueid = srv.siteregionvenueid
and std.slotnumber in (1,2,3,4,5,6,7,8,9,10,11,12,13,14)) as [% Blank],
case (isNull(sum(tmptbl.maxcount), 0) * (datediff(day, @startdate, @enddate) + 1) as TotalSlots) when 0 then 1
else (isNull(sum(tmptbl.maxcount), 0) * (datediff(day, @startdate, @enddate) + 1) as TotalSlots)

this is the statement before I tried putting this case statement in:
where
thedate between sf.startdate and sf.enddate
and da.deletedate is null
and da.siteregionvenueid = srv.siteregionvenueid
and std.slotnumber in (1,2,3,4,5,6,7,8,9,10,11,12,13,14)) as [% Blank],
isNull(sum(tmptbl.maxcount), 0) * (datediff(day, @startdate, @enddate) + 1) as TotalSlots

Can anybody help me try to make totalslots 1 when it is 0??

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-03-09 : 14:13:29

Don't you need the case statement in your select part and not in the where clause?

SELECT YourColumnsToSelect, YourCaseStatement
FROM ...
WHERE ...

Tara Kizer

cjhardie
Yak Posting Veteran

58 Posts

Posted - 2007-03-09 : 14:16:01

I don't have it in the select statement....It's from a really long query and the only place it can go is in the where clause.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-03-09 : 14:23:53

Well your statement isn't correct then. You probably need something like this:

where
thedate between sf.startdate and sf.enddate
and da.deletedate is null
and da.siteregionvenueid = srv.siteregionvenueid
and std.slotnumber in (1,2,3,4,5,6,7,8,9,10,11,12,13,14)) and
TotalSlots = case when (isNull(sum(tmptbl.maxcount), 0) * (datediff(day, @startdate, @enddate) + 1)) = 0 then 1
else (isNull(sum(tmptbl.maxcount), 0) * (datediff(day, @startdate, @enddate) + 1))

Note: I did not check if this is syntactically correct.

I don't understand your use of AS in the WHERE clause. AS is used to alias objects. You don't do that in the WHERE clause.

Tara Kizer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources