[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

A TSQL query. MS SQL Server

Posted on 2012-04-02
14
Medium Priority
?
332 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 400 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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

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 400 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 1200 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

649 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