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?

Microsoft SQL Server 2008SQL

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"

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat

William Peck

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.

Log in or sign up to see answer

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

or

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

ask a question
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