sbp/dbp 1

[Edski]

Hi,
I have a table of blood pressures, recorded separately as systolic and diastolic pressures (two different fields). Also in the table is the date and time of measurement. There are many measurements per day.

For each day, I'd like to retrieve the minimum sbp value and the corresponding dbp value (same time and day). If there are ties in the sbp value, I want the minumum dbp value.

Example:
3 measurements for day1 are 90/70, 100/80, 100/50. The pair I want is 90/70, (not 100/50).
For day2 the measurements are 90/70, 90/60, 100/50. The pair I want is 90/60.

I thought that this would be simple, but I'm having difficulty. I'll keep at it but if anyone would like to have a go then I'd be grateful for saving me any more time wasted.

 

[gmmastros]

Well, I happen to know a little bit about blood pressure. (but only a little).

For example, I know that...

the systolic and diastolic values are usually integers.
I also know that these numbers can never be negative.
I know that these number can never be greater than 999

Using this Information, we can do a little math on these numbers to get what you are looking for.

For example, if you multiply the systolic by 1000 and add the diastolic, then get the min value, it will be the number you are looking for. Check it out...

Select Min(1000 * sbp + dbp) From Table

Of course, this isn't completely what you are looking for. You want the data. So...

Select * 
from   BloodPressureTable
Where  sbp * 1000 + dbp = 
            (
            Select Min(sbp * 100000 + dbp)
            From Bloodpressure
            )

I will assume you know how to group the data by date. If you need help with this, let me know.

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Edski]

gmmastros,
That's very clever!

I think I also got the answer by nesting a groupby query within a another groupby query and JOINING the result back to the original table. But I'll try your solution and compare accuracy and speed!

Thanks again....Edski

 

[gmmastros]

I was playing around with this a little. Here's the data I used...

Table Name = BloodPressure
[tt]
BloodPressureId DateStamp SBP DBP
1 3/21/2006 10:00:00 AM 90 70
2 3/21/2006 11:00:00 AM 100 80
3 3/21/2006 12:00:00 PM 100 50
4 3/21/2006 1:00:00 PM 90 60
5 3/22/2006 1:00:00 PM 120 80
6 3/22/2006 2:00:00 PM 130 90
7 3/22/2006 3:00:00 PM 140 90
8 3/22/2006 4:00:00 PM 120 70
[/tt]

The query I came up with...

Select Datestamp, SBP, DBP
From   BloodPressure 
       Inner Join 
         (
          SELECT Min([sbp]*100000+[dbp]) As MinBloodPressure
          FROM   BloodPressure
          GROUP BY Format(BloodPressure.DateStamp, "yyyy-mm-dd")
         ) As A 
           On BloodPressure.[sbp]*100000+BloodPressure.[dbp] = A.MinBloodPressure

The results I got were....
[tt]
Datestamp SBP DBP
3/21/2006 1:00:00 PM 90 60
3/22/2006 4:00:00 PM 120 70
[/tt]

I'm curious to know how the performance compares to nested subqueries with group by's.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Ooops. I missed a join. Here's the corrected version.

Select Datestamp, 
       SBP, 
       DBP 
from   BloodPressure 
       Inner Join 
         (
         SELECT Min([sbp]*100000+[dbp]) As MinBloodPressure,
                Format(DateStamp, "yyyy-mm-dd") As TheDate
         FROM BloodPressure
         GROUP BY Format(BloodPressure.DateStamp, "yyyy-mm-dd")
         ) As A 
         On BloodPressure.[sbp]*100000+BloodPressure.[dbp] = A.MinBloodPressure 
         And Format(BloodPressure.DateStamp, "yyyy-mm-dd") = A.TheDate

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Crowley16]

can't you just use a multiple order by statement and the top clause?

select top 1 * from table
order by sbp, dbp



--------------------
Procrastinate Now!

 

[Edski]

I can, if it will work with the groupby statement.

...many ways to skin a cat...