Solved

# Some TSQL

Posted on 2011-09-02
235 Views

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 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.
0
Question by:soozh
• 7
• 7

LVL 59

Expert Comment

ID: 36473292
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

LVL 8

Expert Comment

ID: 36482807
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
``````

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

Author Comment

ID: 36501119
I do not know which period the report will be run.. so i can not have dates in the TSQL
0

LVL 59

Expert Comment

ID: 36502431
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

Author Comment

ID: 36582709
I have been sidetracked on this issue but i hope to take it up next week.
0

LVL 59

Expert Comment

ID: 36583118
Sounds good. See you then.
0

Author Comment

ID: 36918471
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

LVL 59

Expert Comment

ID: 36918748
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

Author Comment

ID: 36942378
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

Author Comment

ID: 36942480
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

LVL 59

Expert Comment

ID: 36944830
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

Author Comment

ID: 36998226
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

LVL 59

Accepted Solution

Kevin Cross earned 500 total points
ID: 37003428
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

Author Comment

ID: 37005862

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

LVL 59

Expert Comment

ID: 37006058
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

## Featured Post

### Suggested Solutions

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.