A TSQL query. MS SQL Server

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?
soozhCEOAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gplanaCommented:
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.
soozhCEOAuthor Commented:
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.
gplanaCommented:
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
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

soozhCEOAuthor Commented:
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.
gplanaCommented:
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.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

soozhCEOAuthor Commented:
and which is the most efficent?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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 ...
gplanaCommented:
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.
Anthony PerkinsCommented:
>>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...
Anthony PerkinsCommented:
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?
soozhCEOAuthor Commented:
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?
Anthony PerkinsCommented:
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.
Anthony PerkinsCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.