Solved

some tsql

Posted on 2013-01-08
6
165 Views
Last Modified: 2013-01-22
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?
0
Comment
Question by:soozh
  • 3
  • 2
6 Comments
 
LVL 18

Expert Comment

by:x-men
ID: 38755316
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
 

Author Comment

by:soozh
ID: 38755537
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
 
LVL 18

Expert Comment

by:x-men
ID: 38755627
group by clinic?

I not understanding what you mean by "distinct count"
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:soozh
ID: 38755677
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
 
LVL 18

Accepted Solution

by:
x-men earned 500 total points
ID: 38755760
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
 
LVL 32

Expert Comment

by:awking00
ID: 38756275
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

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question