| Author |
 Topic  |
|
|
scottjon
Starting Member
United Kingdom
3 Posts |
 Posted - 03/26/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 03/26/2012 : 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
United Kingdom
3 Posts |
Posted - 03/26/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 03/26/2012 : 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
India
47023 Posts |
Posted - 03/26/2012 : 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/
|
|
|
| |
Topic |
|
|
|
Ending case when statement
