Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

some tsql

Posted on 2013-01-08
6
Medium Priority
?
171 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

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 1000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

877 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