Results 1 to 15 of 15
  1. #1
    Join Date
    Nov 2009
    Posts
    23

    Question Unanswered: help with a stored procedure

    OK, here is the problem:

    I am trying to return a list of vital signs that fall outside outliers (Protocols.LowLimit, Protocols.HighLimit). Cetain vital sign records are "grouped" in the data meaning that they are taken from the patient together (e.g. Systolic and Diastolic blood pressure). Here is the current SP for returning "flagged" vital signs (vital signs that lie outside the parameters)

    Code:
    SELECT v.*, pp.PhysicianID, p.*
    FROM DBO.VitalSigns v
    LEFT JOIN DBO.PatientPhysicians pp ON v.PatientID = pp.PatientID
    LEFT JOIN DBO.Protocols p ON v.VitalSignTypeID = p.VitalSignTypeID AND (v.PatientID = p.PatientID OR p.PatientID is null)
    LEFT JOIN Patients pa on v.PatientID = pa.PatientID
    WHERE v.Value < p.LowLimit OR v.Value > p.HighLimit
    AND pa.IsDeleted IS NULL OR pa.IsDeleted = 0
    order by daterecorded DESC, GroupID DESC
    The problem is, sometimes only the systolic vital is returned without the diastollic, and vice versa. When the flagged vitals are returned, I also need to join any records that have a matching "GroupID" even if that particular vital doesn't violate the high and low parameters.

    Is any of this making sense?

  2. #2
    Join Date
    Nov 2009
    Posts
    23
    Forgot to mention, the reason that only the systolic record is returned is because the diastolic doesn't violate the protocol limits. But I really need both values because one is worthless without the other when I report the data to the physicians.

  3. #3
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    In your ORDER BY clause, which tables are these two columns coming from?

    For an initial shot at clean-up, just so that I can see what is going on here (note the default criteria you are going to have to supply in the WHERE criteria, for NULL limits):

    Code:
    SELECT  v.*
            ,pp.PhysicianID
            ,p.*
    FROM    DBO.VitalSigns v
    LEFT
    JOIN    DBO.PatientPhysicians pp ON
                v.PatientID = pp.PatientID
    LEFT
    JOIN    DBO.Protocols p ON
                v.VitalSignTypeID = p.VitalSignTypeID
                AND (v.PatientID = p.PatientID OR p.PatientID is null)
    LEFT
    JOIN    Patients pa on
                v.PatientID = pa.PatientID
                AND isnull(pa.IsDeleted,0)=0
    WHERE   v.Value < isnull(p.LowLimit,0)
            OR v.Value > isnull(p.HighLimit,0)
    order
    by      daterecorded DESC
            ,GroupID DESC
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    have you considered an EXISTS clause?
    Dave

  5. #5
    Join Date
    Nov 2009
    Posts
    23
    Quote Originally Posted by PracticalProgram View Post
    In your ORDER BY clause, which tables are these two columns coming from?

    For an initial shot at clean-up, just so that I can see what is going on here (note the default criteria you are going to have to supply in the WHERE criteria, for NULL limits):
    thanks for the reply, I updated the columns in the where clause, here is the updated query


    Code:
    SELECT  v.*
            ,pp.PhysicianID
            ,p.*
    FROM    DBO.VitalSigns v
    LEFT
    JOIN    DBO.PatientPhysicians pp ON
                v.PatientID = pp.PatientID
    LEFT
    JOIN    DBO.Protocols p ON
                v.VitalSignTypeID = p.VitalSignTypeID
                AND (v.PatientID = p.PatientID OR p.PatientID is null)
    LEFT
    JOIN    Patients pa on
                v.PatientID = pa.PatientID
                AND isnull(pa.IsDeleted,0)=0
    WHERE   v.Value < isnull(p.LowLimit,0)
            OR v.Value > isnull(p.HighLimit,0)
    order
    by      v.daterecorded DESC
            ,v.GroupID DESC
    Quote Originally Posted by Dave
    have you considered an EXISTS clause?
    but how would that work exactly? I'm having a hard time wrapping my brain around pulling records where the GroupID is the same as the GroupID in records that already being pulled...Maybe I'm making this too complicated? Wouldn't be the first time...

  6. #6
    Join Date
    Nov 2009
    Posts
    23
    @PracticalProgram

    I think including the isnull for lowlimit and highlimit is returning all vital signs even ones that dont' violate a lowlimit or highlimit....

  7. #7
    Join Date
    Nov 2009
    Posts
    23
    bump, any more ideas?

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Create a "grouping table" that identifies vital measurements that are part of a group. So systolic and diastolic will be part of the blood pressure group, while temperature will be the only member of a temperature group. Identify which groups have one or more measurements outside of the established limits, then report the whole group.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Nov 2009
    Posts
    23
    Pat, are you talking about a temporary table that only exists at runtime inside the stored procedure or an actual table? I'm kind of confused since all vital signs are identical, except for the fact that some have groupIDs and some do not. Wouldn't I be storing redundant data if I created this table?

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by jarrette View Post
    But I really need both values because one is worthless without the other when I report the data to the physicians.
    If you need to report both values (or more specifically all of the values in a given group) when any value is out of range, then you need some way to identify the groups and the values within those groups. As a side benefit, this could also allow you to detect when you only have part of a group such as having only the diastolic measurement without a matching systolic measurement.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  11. #11
    Join Date
    Nov 2009
    Posts
    23
    In a temporary table, right? So separate out all the vitals with a group ID and put them into a temp table?

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Your PatientPhysicians table is used to link one person (a patient) to another person (a Physician). The VitalGroup would be used to link one VitalSignMeaure (such as systolic) to another VitalSignMeasure (such as diastolic). Wherever you store the PatientPhysicians data, you should also store your VitalGroup data.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  13. #13
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    How about making this a lot less complicated and take a look at the exists as I mentioned earlier.

    Code:
    select cols_that_you_want
        from what_ever_tables
    where conditions that join your tables
    
       AND (EXISTS(THE SQL statement that finds THE VALUE BEING LESS THAN
                             WHAT IS ALLOWED)
           OR EXISTS(THE SQL statement that finds THE VALUE BEING GREATER 
                             THAN WHAT IS ALLOWED))
    Dave

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Dave:

    Maybe I'm missing something, but what I understand jarette to want is a bit more complicated.

    Think of the individual measurements as students, and the groups of measurements as study groups. What I understand jarrette to want is the list of all students in a study group that has any student outside of the accepted range. If a study group only has one student, then only that one student needs to be reported. If the study group has five students and one of them is outside of the accepted range, then the whole study group needs to be reported.

    In jarette's example, body temperature is a single measurement. If it is outside of the acceptable range, then only the temperature needs to be shown on the report. However, the systolic and diastolic measurements combined measure the blood pressure and if either of them is outside the acceptable limits for that measure then both the systolic and diastolic values need to be reported.

    That's why I think that the groups need to be stored. The SELECT statement could then use a subquery to find values outside the range, then report all of the measurements in any groups that are returned by that subquery.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  15. #15
    Join Date
    May 2010
    Posts
    1
    Think of the individual measurements as students, and the groups of measurements as study groups.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •