case statement within aggregate

[mgm]

Having a problem getting this to work. I tried isnull to show 0 value when
a count (per year) was none... any ideas? (this is my last case try)

select 'Year - Count' =
case when count(distinct casekey) = 0 then Cast(year(filedate) as
varchar(4)) + '0'
else cast(year(filedate) as varchar(4)) + ' - ' + cast(count(distinct
casekey) as varchar (20)) end
from casemaster (nolock)
where filedate >= @stDate and filedate <= @endDate
and locationid = @locationid
and noscode = @noscode
group by year(filedate)
order by 1

 

[John W. Vinson]

Having a problem getting this to work. I tried isnull to show 0 value when
a count (per year) was none... any ideas? (this is my last case try)

select 'Year - Count' =
case when count(distinct casekey) = 0 then Cast(year(filedate) as
varchar(4)) + '0'
else cast(year(filedate) as varchar(4)) + ' - ' + cast(count(distinct
casekey) as varchar (20)) end
from casemaster (nolock)
where filedate >= @stDate and filedate <= @endDate
and locationid = @locationid
and noscode = @noscode
group by year(filedate)
order by 1

This is valid SQL/Server dialect SQL, but neither CASE nor CAST nor
COUNT(DISTINCT...) nor the @ parameter syntax are supported in the Jet dialect
of Access. Are you using passthrough queries, or do you have SQL compatiblity
set? Or are you using Access at all...???

 

[mgm]

I guess I am in the wrong discussion group... i get lost trying to find the
proper group in here!