Solved

some tsql

Posted on 2013-01-08
6
164 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

863 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now