Trouble with a count

[dmorand]

Ok, I'm probably just being dumb, but I'm officially stumped on this one.

I have a table filled with a bunch of diagnosis codes for people. I want to get a count of how many codes a person might have had, but I only want to increase the counter if the code exists in a list of 10 codes. How do I go about doing this?

[code=sql]
select distinct b.encmrno,b.rpt encstrdtime,
--This is the count I need to modify
count(a.dxcdcal ) as 'Count of DX',
(case
when a.dxcdval in ('V76.12','V76. 11') then 'Screening'
when a.dxcdval in ('611.8','610.1 ','611.1','611. 2','611.3',
'611.4','611.5' ,'611.6','611.7 1',
'611.72','611.7 9','611.9','793 .80',
'793.81','793.8 9','V16.3','V10 .3','V15.89') then 'Diagnostic'
end) as 'Mammo'
from smsdss.dxprocv a
inner join smsdss.encv b on a.encobjid=b.en cobjid
where b.rptencstrdtim e >= '2007-01-01 00:00:00' and b.rptencstrdtim e <= '2007-12-31 23:59:59'
and a.dxcdval in ('V76.12','V76. 11','611.8','61 0.1','611.1','6 11.2','611.3',
'611.4','611.5' ,'611.6','611.7 1',
'611.72','611.7 9','611.9','793 .80',
'793.81','793.8 9','V16.3','V10 .3','V15.89')
group by b.encmrno,b.rpt encstrdtime,a.d xcdval
[/code]

[radcaesar]

Select the count(code)
Grouby userid

Make the above in a sp and return the reult.
Based on the result increment your counter.

I was not aware of your table schema. i.e. How it was designed.

Ok, I'm probably just being dumb, but I'm officially stumped on this one.

I have a table filled with a bunch of diagnosis codes for people. I want to get a count of how many codes a person might have had, but I only want to increase the counter if the code exists in a list of 10 codes. How do I go about doing this?

[code=sql]
select distinct b.encmrno,b.rpt encstrdtime,
--This is the count I need to modify
count(a.dxcdcal ) as 'Count of DX',
(case
when a.dxcdval in ('V76.12','V76. 11') then 'Screening'
when a.dxcdval in ('611.8','610.1 ','611.1','611. 2','611.3',
'611.4','611.5' ,'611.6','611.7 1',
'611.72','611.7 9','611.9','793 .80',
'793.81','793.8 9','V16.3','V10 .3','V15.89') then 'Diagnostic'
end) as 'Mammo'
from smsdss.dxprocv a
inner join smsdss.encv b on a.encobjid=b.en cobjid
where b.rptencstrdtim e >= '2007-01-01 00:00:00' and b.rptencstrdtim e <= '2007-12-31 23:59:59'
and a.dxcdval in ('V76.12','V76. 11','611.8','61 0.1','611.1','6 11.2','611.3',
'611.4','611.5' ,'611.6','611.7 1',
'611.72','611.7 9','611.9','793 .80',
'793.81','793.8 9','V16.3','V10 .3','V15.89')
group by b.encmrno,b.rpt encstrdtime,a.d xcdval
[/code]