Ending case when statement - 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

General SQL Server Forums

New to SQL Server Programming

Ending case when statement

Author

Topic

scottjon
Starting Member

3 Posts

Posted - 2012-03-26 : 06:50:07

Hi,

I am trying to end a case when statement with nested queries and I can't get the ending right and get the error message; Incorrect syntax near the keyword 'group'...Any assistance gratefully received.

and case
when locode='DB' then
(case
when datename(dw,wdate)='Friday' then 'N'
when datename(dw,wdate)='Saturday' then 'N'
else 'Y' end)
else
(case when tc.tkcal ='SA' then
(case
when datename(dw,wdate)='Thursday' then 'N'
when datename(dw,wdate)='Friday' then 'N'
else
(case
when datename(dw,wdate)='Saturday' then 'N'
when datename(dw,wdate)='Sunday' then 'N'
else 'Y' end) end

group by loregion1,
ldesc ,tkloc,

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-26 : 06:59:41

Don't know about the logic, but you need two more brackets and two more "END"s.

case
when locode='DB' then
(case
when datename(dw,wdate)='Friday' then 'N'
when datename(dw,wdate)='Saturday' then 'N'
else 'Y' end)
else
(case when tc.tkcal ='SA' then
(case
when datename(dw,wdate)='Thursday' then 'N'
when datename(dw,wdate)='Friday' then 'N'
else
(case
when datename(dw,wdate)='Saturday' then 'N'
when datename(dw,wdate)='Sunday' then 'N'
else 'Y' end) end ) END ) END

scottjon
Starting Member

3 Posts

Posted - 2012-03-26 : 07:09:26

Hi,

Probably worth sending all the code. I have added the parts suggested below and now get;

Msg 4145, Level 15, State 1, Line 98
An expression of non-boolean type specified in a context where a condition is expected, near 'group'.

declare @earliest datetime
declare @to datetime
declare @days int

set @earliest = '01 March 2011'
set @to = dbo.cf_getdateonly(getdate())-1
set @days = 28

select detail.Region,
detail.Office,
detail.PGroup,
count(*) [No_of_Fee_Earners],
sum(Last20) [Total_Last20],
round(avg(Days),1) [Average_Outstanding],
sum(Prior20) [Total_Prior20],
sum(Offset) [Offset]
from
(
select
loregion1 [Region],
tkloc [loc],
ldesc [Office],
case when loregion1 = 'LON' then
udvalue1
else ''
end
[PGroup]
,wtkinit [Initials]
,sum(7-(isnull(tdb,0)+isnull(tdnb,0))) [Missing_Hours]
,sum(CEILING(( 7-(isnull(tdb,0)+isnull(tdnb,0)))/7)) [Days]
,sum(case when wdate >=(@to-@days-isnull(offset,0))
then 1
else 0
end ) [Last20]
,sum(case when wdate <(@to-@days-isnull(offset,0))
then 1
else 0
end)[Prior20] ,
sum(offset) [Offset]
from
(select
wdate,tkinit wtkinit

from
lov_cal
,timekeep

where
wdate between @earliest
and @to
and wdate>=tkemdate
and tktmdate is null
and tkeflag='Y') w
left join timekeep
on wtkinit=timekeep.tkinit

left join location
on tkloc=locode
left join udfvlst
on tksect = udvalue and udfvlst = 'LNPA'
left join tkdaily
on wdate=tddate
and wtkinit=tdinit
left join
(select lhloc, count(*) offset from lochldy
where lhdate between getdate()-@days and getdate() group by lhloc) offset
on lhloc = tkloc

left join timecal as tc
on wtkinit = tc.tkinit

where
wdate not in
(select lhdate from lochldy where lhloc=locode)
and 7-(isnull(tdb,0)+isnull(tdnb,0))>0

and case
when locode='DB' then
(case
when datename(dw,wdate)='Friday' then 'N'
when datename(dw,wdate)='Saturday' then 'N'
else 'Y' end)
else
(case when tc.tkcal ='SA' then
(case
when datename(dw,wdate)='Thursday' then 'N'
when datename(dw,wdate)='Friday' then 'N'
else
(case
when datename(dw,wdate)='Saturday' then 'N'
when datename(dw,wdate)='Sunday' then 'N'
else 'Y' end) end) end) end

group by loregion1,
ldesc ,tkloc,
case when loregion1 = 'LON' then udvalue1
else ''
end,
wtkinit
) detail

group by Region,Office,PGroup

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-26 : 07:14:57

You have to indicate what the result of the nested CASE expression needs to be compare to - for example:

...
where
wdate not in
(select lhdate from lochldy where lhloc=locode)
and 7-(isnull(tdb,0)+isnull(tdnb,0))>0

and case
when locode='DB' then
(case
when datename(dw,wdate)='Friday' then 'N'
when datename(dw,wdate)='Saturday' then 'N'
else 'Y' end)
else
(case when tc.tkcal ='SA' then
(case
when datename(dw,wdate)='Thursday' then 'N'
when datename(dw,wdate)='Friday' then 'N'
else
(case
when datename(dw,wdate)='Saturday' then 'N'
when datename(dw,wdate)='Sunday' then 'N'
else 'Y' end) end) end) end
 = YesNoColumn


group by loregion1,
ldesc ,tkloc,
case when loregion1 = 'LON' then udvalue1
else ''
end,
wtkinit
) detail

Here, the YesNoColumn is the column from some table in your data.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-26 : 13:07:35

your case expression looks horribly complicated. Can you explain what exactly you're trying to achieve here giving data sample?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources