Solved

A TSQL query. MS SQL Server

Posted on 2012-04-02
14
326 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
 

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 142

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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 142

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

747 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

10 Experts available now in Live!

Get 1:1 Help Now