Posted on 2013-01-08
Using MS SQL Server 2008.
I have a table defined as:
/* Create a table to put the treatments in */
CREATE TABLE #Treatments
-- Result set structure
It holds treatment data for different clinics (identified by kli_kliniknr ).
I have a query that returns the distinct count of EyeId and the total number of treaments per year.
SELECT BehÅr as TreatmentYear,
COUNT(distinct(EyeId)) as EyeCount,
COUNT(*) as TreatmentCount
GROUP BY BehÅr
ORDER BY BehÅr
Now i have to add in the distinct eye count and treament counts for a given clinic. All this code is in a stored procedure. The clinic number is given by the paremeter @kli_kliniknr.
I can figure out that the clinics treament count can be calculated simply by:
SUM(case when kli_kliniknr = @Kli_KlinikNr then 1 else 0 end) as ClinicTreamentCount
but how do i get the distinct count of EyeId on a per year basis for the given clinic?