Distinct Count Issue

[Whippingboy2004]

I'm having an issue with a report I'm trying to write in SQL 2000. I'm trying to determine a count for each letter type (NewCert and ReCert, the final report will have about 10 types) AND want to count each Case only once for each letter type.

SELECT DISTINCT(tl.CaseID),
NewCert=CASE tl.TypeCode WHEN 'N2BI' THEN COUNT(DISTINCT tl.TypeCode) ELSE 0 END,
ReCert=CASE tl.TypeCode WHEN 'N3BI' THEN COUNT(DISTINCT tl.TypeCode) ELSE 0 END
FROM TBL_THStepsLetter tl

GROUP BY tl.CaseID, tl.TypeCode
ORDER BY tl.CaseID

This code may be way off, but I've been trying every possible way to get this to work. Any thoughts would be appreciated.

Thanks a bunch,
T.

 

[mattyboy2000]

Is this what you want?:

SELECT tl.TypeCode, Count(DISTINCT tl.CaseID) as Cases
FROM TBL_THStepsLetter tl
GROUP BY tl.TypeCode

This will list the Type coded and the number of cases.

I think you may have been complicating the matter.

Matt