Solved

Query question

Posted on 2015-02-22
15
70 Views
Last Modified: 2015-02-23
Hello,

MS SQL Server 2008!

I have three database tables that record data regarding patient visits.  The tables hold data from different departments and are different in number of columns and content.

However there are three columns that hold the same type of information in the three tables.  These three columns are PatientId, VisitDate and Height.

I have been asked to produce a query that returns the Height of the patient on their latest visit.

So I need to find the row for each patient in the three tables, identify the latest record using VisitDate and take the value of Height.

The three tables are Hospital, Clinic, and Doctor.

The three columns that are identical are:
Pat_id       int
VisitDate      Date
HeightInCm      int

Can someone suggest a query or a solution?  I just can’t seem to get my head round it!


Thanks
0
Comment
Question by:soozh
  • 8
  • 4
  • 3
15 Comments
 
LVL 33

Expert Comment

by:Mike Eghtebas
Comment Utility
SELECT TOP (1) 
	Pat_id
	, VisitDate
	, HeightInCm
FROM AnyOfTheThreeTables
-- WHERE Pat_id = 'MRN002'  -- for example, if you need for one patient.
ORDER BY VisitDate DESC

Open in new window


Unless there are some other requirements, I do not see any need to query three tables. Apparently all three tables have the information you need.

Mike
0
 

Author Comment

by:soozh
Comment Utility
the three tables have different data... a patient may visit either a hostpital, clinic or doctor and the information is stored in the relevant table.

A typical patient episode can start by they seeing the doctor, then going to hospital, and then to the clinic... but not necessarily.  They may never leave the doctor.

So i have to search all tables to find the latest visit.
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
Comment Utility
This will give you only one patient. Try it to see if it works. Later we need to revise it to return it for all patients:
Select Top(1) Pat_id, VisitDate, HeightInCm
From (SELECT  Top(1) Pat_id, VisitDate , HeightInCm
FROM Hospital Union All
SELECT  Top(1) Pat_id, VisitDate, HeightInCm 
FROM Clinic Union All
SELECT  Top(1) Pat_id, VisitDate, HeightInCm 
FROM Doctor 
ORDER BY VisitDate DESC) as D
ORDER BY VisitDate DESC

Open in new window

0
 
LVL 33

Expert Comment

by:Mike Eghtebas
Comment Utility
try:
WITH t1 AS (
SELECT Pat_id, Max(VisitDate)  As DateVal,Max(HeightInCm) as Height From Hospital GROUP By CUstid
), t2 AS (
SELECT Pat_id, Max(VisitDate)  As DateVal,Max(HeightInCm) as Height From Clinic GROUP By CUstid
), t3 AS (
SELECT Pat_id, Max(VisitDate)  As DateVal,Max(HeightInCm) as Height From Doctor GROUP By CUstid
), t123 AS (
Select Pat_id, DateVal, Height From t1 Union All 
Select Pat_id, DateVal, Height From t2 Union All
Select Pat_id, DateVal, Height From t3
)
SELECT Pat_id, Max(DateVal) as Date_Val, Height FROM t123
Group By Pat_id, Height

Open in new window


We need to take a look at Max(HeightInCm) as Height to see how it behaves.

Also, we may need to do some adjustments  to handle null fields if any.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
I suggest using ROW_NUMBER() for this, it is convenient, efficient and best of all gives you access to the entire row
(which is harder to achieve using MAX() )

What you haven't told me is:
a. how the 3 tables (Hospital, Clinic, and Doctor) JOIN together
b, which table each of the 3 fields comes from, (e.g. does height come from the clinic table? which one has visit date?)

If I had this information I might be able to help further.
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
Comment Utility
Paul,

Thank you for the input. Applying your good suggestion to this question, I have:
SELECT
      *
FROM (
      SELECT Pat_id, VisitDate, HeightInCm, 
	  ROW_NUMBER() OVER (PARTITION BY Pat_id ORDER BY VisitDate DESC) AS rn 
      FROM Hospital
      Union All SELECT Pat_id, VisitDate, HeightInCm, 
	  ROW_NUMBER() OVER (PARTITION BY Pat_id ORDER BY VisitDate DESC) AS rn 
      FROM Clinic  
      Union All SELECT Pat_id, VisitDate, HeightInCm, 
	  ROW_NUMBER() OVER (PARTITION BY Pat_id ORDER BY VisitDate DESC) AS rn
      FROM Doctor    
      ) AS sq
WHERE rn = 1
-- Order By Pat_id
Using a local table [Sales].[Orders], I get something like (with Order By Pat_id included):
CUstid  shippeddate                 freight         rn      <-- to replace Pat_id, VisitDate, HeightInCm, rn 
1	2008-04-13 00:00:00.000	     1.21 	     1
1	2008-04-13 00:00:00.000	     1.21	     1
1	2008-04-13 00:00:00.000	     1.21	     1
2	2008-03-11 00:00:00.000	     39.92	     1
2	2008-03-11 00:00:00.000	     39.92	     1
2	2008-03-11 00:00:00.000	     39.92	     1
3	2008-02-10 00:00:00.000	     58.43	     1
3	2008-02-10 00:00:00.000	     58.43	     1
3	2008-02-10 00:00:00.000	     58.43	     1

Open in new window


Question: How we need now to select the height among the 3 rows we are getting for rn= 1?

CUstid  shippeddate                 freight       rn      <-- to replace Pat_id, VisitDate, HeightInCm, rn
1      2008-04-13 00:00:00.000      1.21       1
1      2008-04-13 00:00:00.000      1.21       1
1      2008-04-13 00:00:00.000      1.21       1

Please note that because I am using the same table 3 times (to mimic Hospital, Clinic, and Doctor tables), these 3 rows above are identical. In reality these 3 most likely will be different dates in each row and we need to pick the latest of all per Pat_id. How do we do that?

Mike
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
Dont use row_number () on every table.
Use it after the 3 have been unioned
You will need an additional 'layer' in the query
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 33

Expert Comment

by:Mike Eghtebas
Comment Utility
rn at first select worked. Third one-only produced error. This because in union first select needs to have aliases. With the other following SELECTs  including the same alias is optional.

I am watching  https://www.youtube.com/watch?v=-m426WYclz8 titled SQLug.se - Itzik Ben-Gan - Creative Uses of the APPLY Operator, session 1. It is very interesting.
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
Comment Utility
About how many rows are there in Hospital, Clinic, and Doctor tables?

You may need to create an index for good performance. Will discuss later.

I am also working on Outer Apply solution to even show Pat_id with no visits (if possible at all) with null VisitDate and HeightInCm.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 250 total points
Comment Utility
This is what I tried to describe earlier (was only using my phone at the time)
SELECT
      Pat_id , VisitDate , HeightInCm
FROM (
      SELECT
            Pat_id , VisitDate , HeightInCm
          , ROW_NUMBER() OVER (PARTITION BY Pat_id ORDER BY VisitDate DESC) AS RN
      FROM (
            SELECT
                  Pat_id , VisitDate , HeightInCm
            FROM Hospital
            UNION ALL
            SELECT
                  Pat_id , VisitDate , HeightInCm
            FROM Clinic
            UNION ALL
            SELECT
                  Pat_id , VisitDate , HeightInCm
            FROM Doctor
            ) AS SQA
      ) AS SQB
WHERE rn = 1
;

Open in new window

0
 
LVL 33

Assisted Solution

by:Mike Eghtebas
Mike Eghtebas earned 250 total points
Comment Utility
Paul,

There was no need for me to test it but I did it any way. Your solution works as always does.

Mike
0
 

Author Comment

by:soozh
Comment Utility
Thanks for all the help.. I dont know where you guys are but its great to wake up on monday morning and find so much work done.  I'll check it out later during the day.
0
 

Author Comment

by:soozh
Comment Utility
So now i have tested and refined the solution.  Thanks it works.  This is what i have done.  As you can see its not about height but a ultrasound measurement.  Undersokningsdatum is the date field.

I have grouped the measurements in to years and ranges.

What i really need now is to make sure i get all the empty rows.  That is for all the years i am interested in and for all the ranges.  How can i do that?

with cte as(
select
      PersonId , Undersokningsdatum , U1MaximalIRAPDiameter
from (
      select
            PersonId , Undersokningsdatum , U1MaximalIRAPDiameter
          , ROW_NUMBER() OVER (PARTITION BY PersonId ORDER BY Undersokningsdatum DESC) AS RN
      from (
            select PersonId , Undersokningsdatum , U1MaximalIRAPDiameter
            from SweAAABas where Approved = 1 and U1Data = 1 and U1MaximalIRAPDiameter is not null
            union ALL
            select PersonId , Undersokningsdatum , U1MaximalIRAPDiameter
            from SweAAAFollowUp where Approved = 1 and U1Data = 1 and U1MaximalIRAPDiameter is not null
            ) AS SQA
      ) AS SQB
where rn = 1
)


select
  Year(t.Undersokningsdatum) as År,
  t.Aneurys as [Aneurys],
  count(t.Aneurys) as [Antal]
from
(
  select Undersokningsdatum,
    case
    when U1MaximalIRAPDiameter < 25 then '<25'
    when U1MaximalIRAPDiameter < 30 then '<30'
    when U1MaximalIRAPDiameter < 35 then '<35'
    when U1MaximalIRAPDiameter < 40 then '<40'
    when U1MaximalIRAPDiameter < 45 then '<45'
    when U1MaximalIRAPDiameter < 50 then '<50'
    when U1MaximalIRAPDiameter < 55 then '<55'
      else '>=55' end as Aneurys
  from cte) t
 
group by  t.Aneurys, Year(Undersokningsdatum)
order by  Year(Undersokningsdatum), t.Aneurys
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
Comment Utility
re: So now i have tested and refined the solution.  Thanks it works.

If it works close this question and post another for your new question.

Mike
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
Some observations:

it is useful to have the full question as the question, this helps improve our solution choices
       - this also makes it faster overall (for all of us)

It does not worry us at all if you use real table names and field names
       - in fact "translating" into terms for us to use can lead to error and misunderstandings
       - and effort for "translating" our responses back to real names is avoided also
       Do use real tables and field names (I have no idea what Undersokningsdatum actually means but I don't need to)

a. "sample data" (cleaned of anything considered private)
b. "expected result" (derived from the sample data)

providing a & b is the fastest way to resolution in my experience.

-------------

tip:
to get all the years for all the patients requires a "Cartesian product" of patients and years
THEN you outer join the query above.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

763 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

12 Experts available now in Live!

Get 1:1 Help Now