soozh
asked on
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 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.
Try following logic
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.
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.
ASKER
I do not know which period the report will be run.. so i can not have dates in the TSQL
Can you answer my question(s) from above, please and we can continue to work on solution I was starting down the path of?
ASKER
I have been sidetracked on this issue but i hope to take it up next week.
Sounds good. See you then.
ASKER
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.
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.
ASKER
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?
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?
ASKER
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?
or should i use a Pivot table?
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 - https://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.
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 - https://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.
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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;
SELECT ...;
SELECT @@ROWCOUNT;
>> 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