SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Ending case when statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

scottjon
Starting Member

United Kingdom
3 Posts

Posted - 03/26/2012 :  06:50:07  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 03/26/2012 :  06:59:41  Show Profile  Reply with Quote
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
Go to Top of Page

scottjon
Starting Member

United Kingdom
3 Posts

Posted - 03/26/2012 :  07:09:26  Show Profile  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 03/26/2012 :  07:14:57  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/26/2012 :  13:07:35  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000