soozh
asked on
some tsql
Hello,
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
kli_kliniknr int,
EyeID varchar(16),
BehÅr int,
BehTyp varchar(50)
)
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
FROM #Treatments
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?
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
kli_kliniknr int,
EyeID varchar(16),
BehÅr int,
BehTyp varchar(50)
)
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
FROM #Treatments
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?
ASKER
ok this i could do... i guess i was not so clear....
i want to add the distinct count for the clinic to the existing select statement... i have a feeling i should use partion by or over
i want to add the distinct count for the clinic to the existing select statement... i have a feeling i should use partion by or over
group by clinic?
I not understanding what you mean by "distinct count"
I not understanding what you mean by "distinct count"
ASKER
The select statement in my question selects counts for each year
It gives a count of the distinct values of EyeId and the count for treatments (which is just the number of records for the year)
But now i want to add the same counts in the statement but for a single clinic. I can count the number of treatments for the clinic with a case statement (see below)... but i do not know how to calculate the count of the distinct values of EyeId for the clinic.
SELECT BehÅr as TreatmentYear,
COUNT(distinct(EyeId)) as EyeCount,
COUNT(*) as TreatmentCount,
-- Now the clinic counts
-- NEED THE COUNT DISTINCT OF EYEID FOR THE CLINIC HERE AS ClinicEyeCount
SUM(case when kli_kliniknr = @Kli_KlinikNr then 1 else 0 end) as ClinicTreamentCount
FROM #Treatments
GROUP BY BehÅr
ORDER BY BehÅr
Hope this clears it up.
It gives a count of the distinct values of EyeId and the count for treatments (which is just the number of records for the year)
But now i want to add the same counts in the statement but for a single clinic. I can count the number of treatments for the clinic with a case statement (see below)... but i do not know how to calculate the count of the distinct values of EyeId for the clinic.
SELECT BehÅr as TreatmentYear,
COUNT(distinct(EyeId)) as EyeCount,
COUNT(*) as TreatmentCount,
-- Now the clinic counts
-- NEED THE COUNT DISTINCT OF EYEID FOR THE CLINIC HERE AS ClinicEyeCount
SUM(case when kli_kliniknr = @Kli_KlinikNr then 1 else 0 end) as ClinicTreamentCount
FROM #Treatments
GROUP BY BehÅr
ORDER BY BehÅr
Hope this clears it up.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Can you provide some sample data and your expected output? I suspect the solution may be found using window functions as you suspect, but might be easier to determine with some test data.
COUNT(distinct(EyeId)) as EyeCount,
COUNT(*) as TreatmentCount
FROM #Treatments
WHERE kli_kliniknr = @Kli_KlinikNr
GROUP BY BehÅr
ORDER BY BehÅr