qsl : datetime that X last became true (and stayed true)

[mastubbs]

Hi all, im using the following sql query to select data from two tables where the most recent value for 'par' is > 6. 

SELECT patients.*, addobs.*, DATE_FORMAT(addobs.datetime, '%d/%m/%Y %H:%i:%s') as dti1 
FROM addobs INNER JOIN patients ON addobs.MRN = patients.MRN
WHERE addobs.datetime = (SELECT MAX(OLAST.datetime) FROM addobs AS OLAST WHERE OLAST.MRN = patients.MRN)
AND addobs.par > 6 
AND NOT addobs.hidden = 'yes'
AND COALESCE(patients.ward,'') != 'dc'
order by addobs.par ASC

I would like to extend this query to also return as 'dti2', the datetime value that par > 6 LAST BECAME true.

 

To clarify, if i have 5 values as follows:

 

          datetime                   par

06-18-2013 05:00:00           7

06-18-2013 04:00:00           8

06-18-2013 03:00:00           7

06-18-2013 02:00:00           2

06-18-2013 01:00:00          10

 

Then the datetime to be returned by my query as dti2 would be 06-18-2013 03:00:00, as this is the time that par>6 LAST BECAME true (and it has STAYED true since this time)

 

This is to be distinguished from 06-18-2013 01:00:00 (which is when PAR>6 FIRST became true)

and also to be distinguished from 06-18-2013 05:00:0 (which is when PAR>6 was LAST true)

 

To give some context for further clarity: PAR is a measure of how unwell a patient is (larger numbers = more unwell). So i am trying to create a list of unwell patients. I want to return patients who have a PAR score of >6 (which is what my query currently achieves) and the amount of time they have had CONSECUTIVE par scores >6.

 

From my limited knowledge of sql i would have thought that this could be done somehow with coalesce but im not sure exactly how. i have tried a few different things with no luck.

 

I hope that all makes sense, i get a little confused even explaining it!

 

Thanks for any help anyone can give in advance,

 

Best,

 

Matt

[kicken]

To do this, you'd want to change the logic around a bit probably. First, find the MAX date of the entries with par <= 6 (ie, the most recent time they were not >6). Then use that to find any entries where par > 6 AND datetime > the MAX of the less than 6 values.

 

Untested, but something like this should get you going in the right direction

SELECT 
   patients.*, 
   addobs.*, 
   DATE_FORMAT(addobs.datetime, '%d/%m/%Y %H:%i:%s') as dti1,
   (SELECT DATE_FORMAT(MIN(addobs.datetime), '%d/%m/%Y %H:%i:%s') FROM addobs WHERE addobs.datetime >= lastOkPar.lastDatetime AND addobs.part > 6 AND addobs.hidden != 'yes') as dti2
FROM addobs 
INNER JOIN patients ON addobs.MRN = patients.MRN
LEFT JOIN (
  SELECT
    addobs.MRN 
    , MAX(addobs.datetime) as lastDatetime
  FROM addobs
  WHERE
    addobs.par <= 6
    AND addobs.hidden != 'yes'
  GROUP BY addobs.MRN
) lastOkPar ON lastOkPar.MRN = addobs.MRN
WHERE 
   addobs.datetime = (SELECT MAX(OLAST.datetime) FROM addobs AS OLAST WHERE OLAST.MRN = patients.MRN)
   AND addobs.par > 6 
   AND NOT addobs.hidden = 'yes'
   AND COALESCE(patients.ward,'') != 'dc'
order by addobs.par ASC

[mastubbs]

To do this, you'd want to change the logic around a bit probably. First, find the MAX date of the entries with par <= 6 (ie, the most recent time they were not >6). Then use that to find any entries where par > 6 AND datetime > the MAX of the less than 6 values.

 

Untested, but something like this should get you going in the right direction

SELECT 
   patients.*, 
   addobs.*, 
   DATE_FORMAT(addobs.datetime, '%d/%m/%Y %H:%i:%s') as dti1,
   (SELECT DATE_FORMAT(MIN(addobs.datetime), '%d/%m/%Y %H:%i:%s') FROM addobs WHERE addobs.datetime >= lastOkPar.lastDatetime AND addobs.part > 6 AND addobs.hidden != 'yes') as dti2
FROM addobs 
INNER JOIN patients ON addobs.MRN = patients.MRN
LEFT JOIN (
  SELECT
    addobs.MRN 
    , MAX(addobs.datetime) as lastDatetime
  FROM addobs
  WHERE
    addobs.par <= 6
    AND addobs.hidden != 'yes'
  GROUP BY addobs.MRN
) lastOkPar ON lastOkPar.MRN = addobs.MRN
WHERE 
   addobs.datetime = (SELECT MAX(OLAST.datetime) FROM addobs AS OLAST WHERE OLAST.MRN = patients.MRN)
   AND addobs.par > 6 
   AND NOT addobs.hidden = 'yes'
   AND COALESCE(patients.ward,'') != 'dc'
order by addobs.par ASC

 

Ok so ive been playing about with this to get it working and i've made a few minor changes (eg rather than par cut-off being set at 6, it's now a variable $par so that the user can change the cut-off), other than that its pretty much the same query as kicken suggested. However i am getting some very odd results from it:

 

1) The returned results aren't ordered by ascending par. I cant actually work out any obvious logic as to how they are ordered. Interestingly, reordering by DESC does change the order of the results, but does not order them by desc either.

 

2) Oddly, the dti2 results are mostly right however for the first row the dti2 result returned corresponds to the wrong mrn. All the other columns in the result do correctly correspond to the mrn displayed (eg most recent value for par, most recent par date/time, other corresponding columns from the table [like patients name])  but not the dti2. It is displaying a result that does exist in the table, but is associated with a different mrn. Odd because as far as i can tell it only happens in the first row returned, and then the dti2 results for the following rows correspond to the correct patients.

 

Im not sure if anyone better than me at php/sql has any ideas about why this is happening? I've been messing around with it for a weeks but havn't figured it out!

 

Thanks in advance for any help with this.

 

Matt

$Find_Query2 = mysql_query("SELECT 
   patients.*, 
   addobs.*, 
   DATE_FORMAT(addobs.datetime, '%d/%m/%Y %H:%i:%s') as dti1,
   (SELECT DATE_FORMAT(MIN(addobs.datetime), '%m/%d/%Y %H:%i:%s') FROM addobs WHERE addobs.datetime >= lastOkPar.lastDatetime AND addobs.par >= $par AND addobs.hidden != 'yes') as dti2
FROM addobs 
INNER JOIN patients ON addobs.MRN = patients.MRN
LEFT JOIN (
  SELECT
    addobs.MRN 
    , MAX(addobs.datetime) as lastDatetime
  FROM addobs
  WHERE
    addobs.par < $par
    AND addobs.hidden != 'yes'
  GROUP BY addobs.MRN
) lastOkPar ON lastOkPar.MRN = addobs.MRN
WHERE 
   addobs.datetime = (SELECT MAX(OLAST.datetime) FROM addobs AS OLAST WHERE OLAST.MRN = patients.MRN)
   AND addobs.par >= $par 
   AND NOT addobs.hidden = 'yes'
   AND COALESCE(patients.ward,'') != 'dc'
order by addobs.par ASC");

[kicken]

2) Oddly, the dti2 results are mostly right however for the first row the dti2 result returned corresponds to the wrong mrn. All the other columns in the result do correctly correspond to the mrn displayed (eg most recent value for par, most recent par date/time, other corresponding columns from the table [like patients name])  but not the dti2. It is displaying a result that does exist in the table, but is associated with a different mrn. Odd because as far as i can tell it only happens in the first row returned, and then the dti2 results for the following rows correspond to the correct patients.

The sub query for dti2 does not take the MRN into account when locating it's data. You'll need to add that as a condition in the WHERE so that it correctly matches the same MRN numbers.

 

   (SELECT DATE_FORMAT(MIN(addobs.datetime), '%m/%d/%Y %H:%i:%s') FROM addobs WHERE addobs.mrn = patients.mrn AND addobs.datetime >= lastOkPar.lastDatetime AND addobs.par >= $par AND addobs.hidden != 'yes') as dti2

[mastubbs]

The sub query for dti2 does not take the MRN into account when locating it's data. You'll need to add that as a condition in the WHERE so that it correctly matches the same MRN numbers.

 

   (SELECT DATE_FORMAT(MIN(addobs.datetime), '%m/%d/%Y %H:%i:%s') FROM addobs WHERE addobs.mrn = patients.mrn AND addobs.datetime >= lastOkPar.lastDatetime AND addobs.par >= $par AND addobs.hidden != 'yes') as dti2

 

Amazing thank you. So the fact that some of the dti2 results were for the correct patients was by chance?

 

I have been playing around trying to order the results by par and then dti2, but no joy. I would have thought that this would work?

$Find_Query2 = mysql_query("(SELECT 
   patients.*, 
   addobs.*, 
   DATE_FORMAT(addobs.datetime, '%d/%m/%Y %H:%i:%s') as dti1,
   (SELECT DATE_FORMAT(MIN(addobs.datetime), '%m/%d/%Y %H:%i:%s') FROM addobs WHERE addobs.mrn = patients.mrn AND addobs.datetime >= lastOkPar.lastDatetime AND addobs.par >= $par AND addobs.hidden != 'yes') as dti2
FROM addobs 
ORDER by patient.par ASC, dti2 ASC
INNER JOIN patients ON addobs.MRN = patients.MRN
LEFT JOIN (
  SELECT
    addobs.MRN 
    , MAX(addobs.datetime) as lastDatetime
  FROM addobs
  WHERE
    addobs.par < $par
    AND addobs.hidden != 'yes'
  GROUP BY addobs.MRN
) lastOkPar ON lastOkPar.MRN = addobs.MRN
WHERE 
   addobs.datetime = (SELECT MAX(OLAST.datetime) FROM addobs AS OLAST WHERE OLAST.MRN = patients.MRN)
   AND addobs.par >= $par 
   AND NOT addobs.hidden = 'yes'
   AND COALESCE(patients.ward,'') != 'dc'
)    order by addobs.par ASC
");

Any idea how i can get the order right?

 

Thanks again for the help with this.

 

Matt

Edited July 8, 2013 by mastubbs

[mastubbs]

Sorry, i meant this. Still doesn't work though :-(

$Find_Query2 = mysql_query("SELECT 
   patients.*, 
   addobs.*, 
   DATE_FORMAT(addobs.datetime, '%d/%m/%Y %H:%i:%s') as dti1,
   (SELECT DATE_FORMAT(MIN(addobs.datetime), '%m/%d/%Y %H:%i:%s') FROM addobs WHERE addobs.mrn = patients.mrn AND addobs.datetime >= lastOkPar.lastDatetime AND addobs.par >= $par AND addobs.hidden != 'yes') as dti2
FROM addobs 
ORDER by addobs.par ASC, dti2 ASC
INNER JOIN patients ON addobs.MRN = patients.MRN
LEFT JOIN (
  SELECT
    addobs.MRN 
    , MAX(addobs.datetime) as lastDatetime
  FROM addobs
  WHERE
    addobs.par < $par
    AND addobs.hidden != 'yes'
  GROUP BY addobs.MRN
) lastOkPar ON lastOkPar.MRN = addobs.MRN
WHERE 
   addobs.datetime = (SELECT MAX(OLAST.datetime) FROM addobs AS OLAST WHERE OLAST.MRN = patients.MRN)
   AND addobs.par >= $par 
   AND NOT addobs.hidden = 'yes'
   AND COALESCE(patients.ward,'') != 'dc'    
");

Thanks

 

Matt