Link to home
Start Free TrialLog in
Avatar of soozh
soozhFlag for Sweden

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?
Avatar of x-men
x-men
Flag of Portugal image

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
Avatar of soozh

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
group by clinic?

I not understanding what you mean by "distinct count"
Avatar of soozh

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.
ASKER CERTIFIED SOLUTION
Avatar of x-men
x-men
Flag of Portugal image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.