Solved

A TSQL query. MS SQL Server

Posted on 2012-04-02
14
328 Views
Last Modified: 2012-04-10
Hello,

I am using MS SQL Server 2008.

I have two tables.  One is Patients, and the other is Visits.

The Patients table hold a list of patients which are uniquely identified using the pat_id column.  The remainer of the table hold name, address etc.

The Visits table hold data regarding patient visits.  It also has the pat_id column as a foriegn key into the Patients table.

The other columns that are interest are:

VisitDate : The data of the visit
Eye: a char(1).  Can be 'L' (left) or 'R' (right)
Origin: a char(1).  Set 'T' for the patients original visit. 'F' in all other cases.

I need to write a query that will return a summary of the patient visits.  It should contain the following colums:

pat_id
... other patient information ..
Number of visits
Number of visits for left eye
Number of visits for right eye
Lastest visit for left eye
Lastest visit for right eye
Earliest visit for left eye
Earliest visit for right eye
Date of visit for left eye where Origin = 'T'
Date of visit for right eye where Origin = 'T'

Note that a patient may only have visits for one eye.. though i guess this does not effect the query.

Can anyone put me together a query that will do the above?
0
Comment
Question by:soozh
  • 4
  • 4
  • 4
  • +1
14 Comments
 
LVL 15

Expert Comment

by:gplana
ID: 37795260
Why don't you user different queries ?

For example this should be to get the number of visits:

SELECT Count(*) AS NumberOfVisits
FROM Visits;

and this would be to get number of visits for left eye:
SELECT Count(*) AS NumberOfVisits
FROM Visits
WHERE eye ='L';

and this to get the last visit for left eye:
SELECT *
FROM Visits
WHERE date = (SELECT MAX(date) FROM Visits)
   AND Eye = 'L';

I think you can do the rest by modifying these queries, but let me know if you have doubts.

Regards.
0
 

Author Comment

by:soozh
ID: 37795291
Thanks but i quess i did not make it clear but i would like one query... so i need to embed a select in a select... and that is a little beyond me.
0
 
LVL 15

Assisted Solution

by:gplana
gplana earned 100 total points
ID: 37795310
In this case, if you are on SQL Server, you can just do this:

SELECT (SELECT ....), (SELECT ....), ... , (SELECT .....);

So put every SELECT on my previous comment as a field of your main SELECT
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:soozh
ID: 37795319
Hello again.. i see i have forgotten to point out (perhaps the most important thing)... that i want to have the information per patient.

So the row returned show have pat_id and then all the details regarding first/last visit etc.
0
 
LVL 15

Expert Comment

by:gplana
ID: 37795350
Ok, sorry for misunderstanding this. Try something like this:

SELECT pat_id,
           (SELECT count(*) FROM Visits WHERE pat_id = p.pat_id) as totalVisits,
           (SELECT count(*) FROM Visits WHERE pat_id = p.pat_id AND eye = 'L') as totalVisitsForLeftEye,
           (SELECT count(*) FROM Visits WHERE pat_id = p.pat_id AND eye = 'R') as totalVisitsForRightEye,
           (SELECT MAX(date) FROM Visits WHERE pat_id = p.pat_id AND eye = 'L') as latestsVisitForLeftEye,
           (SELECT MAX(date) FROM Visits WHERE pat_id = p.pat_id AND eye = 'R') as latestsVisitForRightEye,
           (SELECT MIN(date) FROM Visits WHERE pat_id = p.pat_id AND eye = 'L') as earliestsVisitForLeftEye,
           (SELECT MIN(date) FROM Visits WHERE pat_id = p.pat_id AND eye = 'R') as earliestsVisitForRightEye,
           (SELECT MIN(date) FROM Visits WHERE pat_id = p.pat_id AND eye='L' AND origin='T') as originDateForLeftEye,
           (SELECT MIN(date) FROM Visits WHERE pat_id = p.pat_id AND eye='R' AND origin='T') as originDateForRightEye
FROM Patients p;

Open in new window



Hope this helps. Regards.
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 100 total points
ID: 37801055
you could also do this:

SELECT p.pat_id
, SUM( CASE WHEN v.pat_id IS NOT NULL THEN 1 ELSE 0 END ) totalVisits,
, SUM( CASE WHEN v.eye = 'L' THEN 1 ELSE 0 END ) totalVisitsForLeftEye,
, SUM( CASE WHEN v.eye = 'R' THEN 1 ELSE 0 END ) totalVisitsForRightEye,
 ... etc ...

FROM Patients p
LEFT JOIN Visits v
   ON v.pat_id = p.pat_id

Open in new window

0
 

Author Comment

by:soozh
ID: 37801189
and which is the most efficent?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 37801298
you have to test that on your server ...
it depends on indexes you have on your table(s).
for example, if on visits you have the clustered index on patid, the queries suggested will run quite fast, normally.
still, I think mine should work "fastest", as it only needs 1 pass for the visits table, not multiple. ...
but then, the optimizer of sql server might "see" what is going on, and make it working the same way internally anyhow.
to be checked with the explain plans ...
0
 
LVL 15

Expert Comment

by:gplana
ID: 37801329
the problem is I don't think you can get thinks like the earliest date for left eye with the latest date for right eye on the same query. I think you have to use subqueries on the SELECT as I have shown on my example.

My solution should be efficient if you have an index for patient field on visits table.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37804363
>>the problem is I don't think you can get thinks like the earliest date for left eye with the latest date for right eye on the same query.<<
That is not correct, as you notice there is no condition in the WHERE clause.

angelIII's query will be the fastest regardless of the index or lack of, even if the SQL QO optimizes the query with the multiple subqueries to look like angelIII's.  And if there are indexes, than it is a no-brainer.  But only you can tell that...
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37804365
Also, if the QO is just going to attempt to make the query with multiple subqueries look like angelIII's, would it not be better to write it that way in the first place?
0
 

Author Comment

by:soozh
ID: 37806585
i think angelIII's query needs a group by statement.

And i also need to return the patients first name, last name...

which means group by p.pat_id, p.fname, p.sname

does this effect anything?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37807634
i think angelIII's query needs a
Yes, that is true.

And i also need to return the patients first name, last name...
That is correct.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 300 total points
ID: 37807638
Or you could do it this way (fix the obvious typo in xSELECT):
xSELECT  p.pat_id ,
        p.fname ,
        p.sname
FROM    Patients p
        LEFT JOIN (xSELECT  pat_id ,
                            SUM(CASE WHEN pat_id IS NOT NULL THEN 1
                                     ELSE 0
                                END) totalVisits ,
                            SUM(CASE WHEN eye = 'L' THEN 1
                                     ELSE 0
                                END) totalVisitsForLeftEye ,
                            SUM(CASE WHEN eye = 'R' THEN 1
                                     ELSE 0
                                END) totalVisitsForRightEye
                    FROM    Visits
                    GROUP BY pat_id
                  ) v ON v.pat_id = p.pat_id

Open in new window

0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

809 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