Some TSQL


I have to produce a report based upon a MS SQL Server 2008 database.

The background is that the database holds measurements made on patients suffering from dementia.    Measurements are made on an irregular basis but usually once per six weeks.

The measurements are used by management to work out how much resources are required to look after these patients.  They want a monthly total and average for all patients they are caring for.

The required output
The attached diagram shows what i am trying to present.   Patients A - E are measured and their monthly values shown in the table.  You can assume a simple table that has:

PatientID integer
MeasuredValue  integer
MeasurementDate  datetime


However there are some difficulties.

1) Not every patient has a measurement in each month.  In that case the measurement from the previous month must be used.

2) The number of patients being treated varies.   For example Patient B does not start treatment until March (Patient E in February), and Patient D has been moved so should not be included in the figures for June and July.

3) If a patient is measured more than once in a month the latest measurement should be used.

4) To find out when a patient arrives at the hospital is simple - it is the date of the first measurement.

5) To find out when a patient leaves a hospital is a little more difficult - but i guess it would be possible to write a function that tested this.  So every time a patient does not have a measurement in a particular month it would be wise to check if they were present at the hospital by calling the function.   (patients can move in and out of the hospital so the presence of measurements further on in time does not mean they are being treated in the current month.


This probably should be worth more than 500 points... but sorry that is the max.
soozhAsked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
In general that works with a LEFT OUTER JOIN to your table or data.

FROM "Dates" d
LEFT OUTER JOIN "Measurements" m ON m.Date = d.Date

But if you have moved all the heavy lifting to a scalar function, then you may need to CROSS JOIN your "Dates" and patient IDs (via "Measurements") in order to call your function with a specific date and patient ID.

e.g., if GetMeasurement takes PatientID and Date in that order (adjust per real function)

SELECT dbo.GetMeasurement(m.PatientID, d.Date)
FROM "Dates" d
CROSS JOIN (
   SELECT DISTINCT PatientID
   FROM "Measurements"
) m

Hopefully that gives you an idea of how that functions, but if not please let me know.
0
 
Kevin CrossChief Technology OfficerCommented:
soozh,

>> 1) Not every patient has a measurement in each month.  In that case the measurement from the previous month must be used.

This is fine. Can be handled in few different ways.

>> 2) The number of patients being treated varies.   For example Patient B does not start treatment until March (Patient E in February), and Patient D has been moved so should not be included in the figures for June and July.

This is the BIG challenge IMHO, but may be simple if you have other data on status and treatment dates of patients stored somewhere. i.e., how can T-SQL determine when to start and stop counting an employee in the report?

3) If a patient is measured more than once in a month the latest measurement should be used.

This can be handled in many ways also. One of which in SQL 2008 is ranking using ROW_NUMBER() OVER(PARTITION BY {month} ORDER BY {full date/time} DESC).

4) To find out when a patient arrives at the hospital is simple - it is the date of the first measurement.

Okay. I see this answers when they start.

5) To find out when a patient leaves a hospital is a little more difficult - but i guess it would be possible to write a function that tested this.  So every time a patient does not have a measurement in a particular month it would be wise to check if they were present at the hospital by calling the function.   (patients can move in and out of the hospital so the presence of measurements further on in time does not mean they are being treated in the current month.

So do you already have this function? If so, then #2 becomes simpler.

--Kevin
0
 
GhunaimaCommented:
Try following logic
select distinct a.patientid, case when b.enddate<'1-jan-2011' then null else (select top 1 measuredvalue from #patient where patientid=a.patientid and measurementdate<'1-feb-2011'  order by measurementdate desc) end Jan
, case when b.enddate<'1-feb-2011' then null else (select top 1 measuredvalue from #patient where patientid=a.patientid and measurementdate<'1-mar-2011' order by measurementdate desc) end Feb
, case when b.enddate<'1-mar-2011' then null else (select top 1 measuredvalue from #patient where patientid=a.patientid and measurementdate<'1-apr-2011' order by measurementdate desc) end Mar
, case when b.enddate<'1-apr-2011' then null else (select top 1 measuredvalue from #patient where patientid=a.patientid and measurementdate<'1-may-2011' order by measurementdate desc) end Apr
, case when b.enddate<'1-may-2011' then null else (select top 1 measuredvalue from #patient where patientid=a.patientid and measurementdate<'1-jun-2011' order by measurementdate desc) end May
, case when b.enddate<'1-jun-2011' then null else (select top 1 measuredvalue from #patient where patientid=a.patientid and measurementdate<'1-jul-2011' order by measurementdate desc) end Jun
, case when b.enddate<'1-jul-2011' then null else (select top 1 measuredvalue from #patient where patientid=a.patientid and measurementdate<'1-aug-2011' order by measurementdate desc) end Jul
, enddate
from #patient a
join (select patientid, MIN(measurementdate) startdate, MAX(measurementdate) enddate from #patient group by PATientid) b on a.patientid=b.patientid

Open in new window


This can assume that the last reading date is the date when patient left the hospital. if you have this date recorded some where replace the subquery with the appropriate query or table.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
soozhAuthor Commented:
I do not know which period the report will be run.. so i can not have dates in the TSQL
0
 
Kevin CrossChief Technology OfficerCommented:
Can you answer my question(s) from above, please and we can continue to work on solution I was starting down the path of?
0
 
soozhAuthor Commented:
I have been sidetracked on this issue but i hope to take it up next week.
0
 
Kevin CrossChief Technology OfficerCommented:
Sounds good. See you then.
0
 
soozhAuthor Commented:
ok now i can continue with this.  should i write a function or stored procedure that chceks if a patient is active that month.  THis will tell me whether the patient has moved to another clinic.
0
 
Kevin CrossChief Technology OfficerCommented:
If the determining factor(s) for a patient being active or not is too complicated for inline code, then yes a function may come in handy. It may not be the best performance-wise, but if you have no choice, I would start there and then just optimize as best you can.
0
 
soozhAuthor Commented:
How do i write the query so i can get a value for all the months between two dates?

I mean is there a group by statement that lets you specify months? And if the period is from 2007-01-01 to 2009-12-31 i need 24 sets of months... so it is really group by year and month.

Or maybe Group By is not the way to go?
0
 
soozhAuthor Commented:
I have seen some examples where you do an outer join against all the possible year/month combinations in a seperate table. Maybe this is the way to go?

or should i use a Pivot table?
0
 
Kevin CrossChief Technology OfficerCommented:
Yes, if the data contains all the possible months, then typically you would GROUP BY Year, Month so that you do not get just 12 rows that is a consolidation of every year for a given month but rather a distinct row by month and year combination.

You are also correct that if there are gaps in the data, a good approach is to have a dates table or use a numbers table similar to what Mark describes in his article - http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_1221-Fun-with-MS-SQL-spt-values-for-delimited-strings-and-virtual-calendars.html - that creates a virtual calendar table that you can then use as your main selection and then left outer join in your actual data table. This will ensure you have every month and year combination accounted for even if data results in 0 sum/count/etc.
0
 
soozhAuthor Commented:
Ok now i have written a function "GetMeasurement" that returns a patients measurement.  You give it a patient and a date and it returns the measurement for that month.

It handles the fact that a patient may not have arrived at the clinic on that date, or that they may have left the clinic on the given date by return NULL.  It also handles the problem when there is no measurement by returning that previous measurement.

I also have a table of dates that i want to know the patients measured value for.

So all i have to do is somehow join the "Dates" table with the "Measurements" table to call my function "GetMeasurement" for each date and each patient.

How would i do that?
0
 
soozhAuthor Commented:

That seems to be working.. but i have a problem with my function.

if i does this:

  select top 1 @msr_Total = msr_Total, @msr_Date = msr_Date from vw_Measurements where pat_Personnummer = @pn and msr_Date <= @date and msr_MeasurementKlinik = @kli_KlinikNr
  order by msr_Date desc;

how do i know if it found a row?  Test for null in one of the variables?
0
 
Kevin CrossChief Technology OfficerCommented:
Correct. If no row, then both @msr_total and @msr_date would be NULL. If these columns can be NULL in a valid row of data, then you may want to add a @found = COUNT(ID_Or_Other_Required_Col) where @found acts as a bit/flag that should either be 1 (because of TOP 1) when true. Not sure if @@ROWCOUNT may be helpful to you also.

SELECT ...;
SELECT @@ROWCOUNT;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.