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?
soozhAsked:
Who is Participating?
 
x-menConnect With a Mentor IT super heroCommented:
SELECT BehÅr as TreatmentYear,
       COUNT(EyeId) as EyeCount,
       COUNT(*) as TreatmentCount,

GROUP BY BehÅr, EyeId

see: http://msdn.microsoft.com/en-us/library/ms177673.aspx
0
 
x-menIT super heroCommented:
SELECT BehÅr as TreatmentYear,
       COUNT(distinct(EyeId)) as EyeCount,
       COUNT(*) as TreatmentCount
       
FROM #Treatments
WHERE kli_kliniknr = @Kli_KlinikNr
GROUP BY BehÅr
ORDER BY  BehÅr
0
 
soozhAuthor Commented:
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
0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
x-menIT super heroCommented:
group by clinic?

I not understanding what you mean by "distinct count"
0
 
soozhAuthor Commented:
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.
0
 
awking00Commented:
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.
0
All Courses

From novice to tech pro — start learning today.