2

I have problem, where i know how to proceed with multiple queries, but dont really now

I Have a table with

  BP {
  name: varchar
  systolic : int
  diastolic : int
  timestamp: date
  }

I need to get the Names from BP table that satisfy the following ANY of the following conditions.

  1. If systolic > 180 OR diastolic > 110
  2. If (systolic >= 140 AND systolic < 180), then take the next 2 readings of systolic, get the average and if that average is >= 140. Then Condition Satisfied.
  3. Same diastolic. If (diastolic >= 90 AND systolic < 110), then take the next 2 readings of diastolic, get the average and if that average is >= 90. Then Condition Satisfied.

There will be more many rows with the same unique name. (meaning many BP Recordings per person).

I can certainly get this working if i jus get all the values and parse it myself in PHP.

But Im wondering if theres a better way to do it directly in MySQL.

My goal here was to find the people that have Hypertension. And those conditions above the guidelines in categorising a person as Hypertensive.

In this example, I simplified the table. And I want to get the name(s) of those that meet these hypertensive guidelines.

Condition 1 is very simple to address. Condition 2 & 3 are whats troubling me.

What I do now

I just get all those values using SELECT and ORDER BY timestamp ASC, name. And parse everything in PHP I simply get the list of people with systolic >=140 AND systolic < 180, Filter them out based on name, order it by timestamps and enumerate through each set, if I find a systolic >= 140 AND systolic < 180, i break enumeration and check the next 2 readings of that name and take the average. I repeat that with diastolic. and Merge the results with all the three conditions, removing any duplicate entries.

I really want to learn if there was a good way to do this in sql itself. Im assuming using a Stored Procedure would be a must. But I'd be glad to know if there was a better way using some kind of subquerying.

5
  • Does the table have a primary or unique key? Perhaps (name, timestamp)? Commented Nov 3, 2015 at 7:38
  • oh yes, lets say the name is unique. In my actual db, we have a familycode. So I take the name and familycode as unique keys.
    – jasonIM
    Commented Nov 3, 2015 at 11:48
  • The name itself cannot be unique. In the question you state that "There will be more many rows with the same unique name" Commented Nov 3, 2015 at 11:52
  • There are many rows for the same Name.
    – jasonIM
    Commented Nov 3, 2015 at 12:09
  • At this point, there isn't a Unique primary key that relates to each row. Sorry, I may have misunderstood your question.
    – jasonIM
    Commented Nov 3, 2015 at 12:10

1 Answer 1

0

Keep the raw data in MySQL. But plan on coding the logic in PHP (or whatever). You have only begun to touch on how complex the math can get. What about the blood pressure readings, plus whether the patient is/was taking a BP lowering drug?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.