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