case statement within aggregate

M

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
 
J

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...???
 
M

mgm

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top