determine last patient temprature

[Jassim Rahma]

I am using this code to determine last patient pulse in my vital_signs table.

-- determine pusle;
SET @pulse = (SELECT TOP 1 pulse FROM vital_signs WHERE file_no = @file_no AND visit_id = @visit_id AND pulse IS NOT NULL ORDER BY vital_id DESC)

IF (@is_child = 'True' AND (@pulse < (SELECT CONVERT(int, system_param_value) FROM system_parameters WHERE system_param_name = 'VITAL_SIGNS_PULSE_ADULT_CHILD_MIN') OR @pulse > (SELECT CONVERT(int, system_param_value) FROM system_parameters WHERE system_param_name = 'VITAL_SIGNS_PULSE_ADULT_CHILD_MAX')))
BEGIN
SET @normal_pulse = 'False'
END
ELSE IF (@patient_gender = 'M' AND (@pulse < (SELECT CONVERT(int, system_param_value) FROM system_parameters WHERE system_param_name = 'VITAL_SIGNS_PULSE_ADULT_MALE_MIN') OR @pulse > (SELECT CONVERT(int, system_param_value) FROM system_parameters WHERE system_param_name = 'VITAL_SIGNS_PULSE_ADULT_MALE_MAX')))
BEGIN
SET @normal_pulse = 'False'
END
ELSE IF (@patient_gender = 'F' AND (@pulse < (SELECT CONVERT(int, system_param_value) FROM system_parameters WHERE system_param_name = 'VITAL_SIGNS_PULSE_ADULT_FEMALE_MIN') OR @pulse > (SELECT CONVERT(int, system_param_value) FROM system_parameters WHERE system_param_name = 'VITAL_SIGNS_PULSE_ADULT_FEMALE_MAX')))
BEGIN
SET @normal_pulse = 'False'
END

It was so easy as you can see :) now I want to do the same for the temprature but the temperature is more than one field unlike the pulse.. I have the following fields: temperature_oral temperature_axillary temperature_rectal temperature_tympanic temperature_skin How can I do the same code above to check the last temp taken from any field. Only one temp will be taken at at time so i need to get that value and the will compare using the above code...

[PIEBALDconsult]

The schema you have will lead to trouble. Have the temperature table as a "transaction" sort of table. Then just query for the most recent. Do the same for the pulse, blood pressure, etc. In fact, probably have one table for all the vital signs, one record per value: ID, PatientID, TakerID, TimeStamp, ReadingType, Value

[Jassim Rahma]

but changing the table structure now is a major work! any solution with the current structure?

[David Mujica]

I'm a bit confused. Is there a timestamp field in your table ? If yes, then you want to find the MAX(timestamp) record and find the value of the temperature. Maybe something with a case statement. (Concept only, syntax is close but not correct) SELECT TempTaken = CASE Temperature_oral WHEN >0 THEN Temperature_Oral Else case Temperature_auxillary When > 0 Then Temperature_Auxillary End End FROM Vital_signs Repeat the series of case statements for each of the temperature methods. Good luck. :thumbsup:

[PIEBALDconsult]

No, fix it now before it becomes really difficult. You can always create views to mimic the current schema.

[Mycroft Holmes]

You are now starting to live with the consequences of lousy data design. This query would be dead simple with a correct data structure.

jrahma wrote:

but changing the table structure now is a major work

If you think it will get any better or take less effort in the future you are insane. The work arounds to service rotten design will mount the longer you leave it, then you need layer more crap on the workarounds, shortly you have a completely unworkable database.

Never underestimate the power of human stupidity RAH