Grouping partially correlated data

[kdbenson]

Tests are given to patients and the time and result of the tests are recorded. Multiple tests can be given to a patient during their visit, which is uniquely identified by the inpatient_data_id. The data might look like this:

inpatient_data_id test_time test_result
1234 2009-06-22 01:41:00 465
1234 2009-06-22 02:41:00 High
1234 2009-06-22 03:41:00 455
3456 2009-06-14 13:26:00 17
3456 2009-06-14 15:26:00 38
5678 2009-07-03 12:12:00 High
5678 2009-07-03 13:12:00 High
5678 2009-07-03 14:12:00 483


If the test results are too high or too low, this needs to be recorded as a critical notification. This notification includes test type, test time, result, and user. These critical notifications are not directly linked to any specific test, only to a patient's visit (inpatient_data_id). The data looks like this:

inpatient_data_id test_type test_time test_result test_user_id
1234 glucose 2009-06-22 01:45:00 465 11111
3456 glucose 2009-06-14 13:26:00 22 22222
3456 glucose 2009-06-14 13:28:00 20 22222
3456 glucose-POCT 2009-06-14 15:28:00 40 33333
3456 glucose-POCT 2009-06-14 16:28:00 37 33333

I cannot link a critical notification directly to a test, so I would like to present the data grouped by the inpatient_data_id, showing tests and critical notifications side-by-side so report consumers can determine themselves which tests relate to which notifications, and follow up with users to enter data correctly. I think it would be helpful to present the data above as follows:

inpatient_data_id test_time test_result test_type test_time test_result test_user_id
1234 2009-06-22 01:41:00 465 glucose 2009-06-22 01:45:00 465 11111
1234 2009-06-22 02:41:00 High NULL NULL NULL NULL
1234 2009-06-22 03:41:00 455 NULL NULL NULL NULL
3456 2009-06-14 13:26:00 17 glucose 2009-06-14 13:26:00 22 22222
3456 2009-06-14 15:26:00 38 glucose 2009-06-14 13:28:00 20 22222
3456 NULL NULL glucose-POCT 2009-06-14 15:28:00 40 33333
3456 NULL NULL glucose-POCT 2009-06-14 16:28:00 37 33333
5678 2009-07-03 12:12:00 High NULL NULL NULL NULL NULL
5678 2009-07-03 13:12:00 High NULL NULL NULL NULL NULL
5678 2009-07-03 14:12:00 483 NULL NULL NULL NULL NULL

Any suggestions for SQL to return this result set?

Thanks,
Kevin

 

[kdbenson]

Code to create and populate example tables (using Teradata) is:

CREATE TABLE crit_care(inpatient_data_id INTEGER, test_type VARCHAR(25), test_time TIMESTAMP, test_result VARCHAR(25), test_user_id INTEGER);
CREATE TABLE poct(inpatient_data_id INTEGER, test_time timestamp, test_result VARCHAR(25));
INSERT INTO crit_care VALUES  (3456,'glucose POCT',TIMESTAMP '2009-06-14 16:28:00','37',33333);
INSERT INTO crit_care VALUES  (3456,'glucose POCT',TIMESTAMP '2009-06-14 15:28:00','40',33333);
INSERT INTO crit_care VALUES  (3456,'glucose',TIMESTAMP '2009-06-14 13:28:00','20',22222);
INSERT INTO crit_care VALUES  (3456,'glucose',TIMESTAMP '2009-06-14 13:26:00','22',22222);
INSERT INTO crit_care VALUES  (1234,'glucose',TIMESTAMP '2009-06-22 01:45:00','465',11111);
INSERT INTO poct VALUES  (5678,timestamp '2009-07-03 14:12:00','483');
INSERT INTO poct VALUES  (5678,timestamp '2009-07-03 13:12:00','High');
INSERT INTO poct VALUES  (5678,timestamp '2009-07-03 12:12:00','High');
INSERT INTO poct VALUES  (3456,timestamp '2009-06-14 15:26:00','38');
INSERT INTO poct VALUES  (3456,timestamp '2009-06-14 13:26:00','17');
INSERT INTO poct VALUES  (1234,timestamp '2009-06-22 03:41:00','455');
INSERT INTO poct VALUES  (1234,timestamp '2009-06-22 02:41:00','High');
INSERT INTO poct VALUES  (1234,timestamp '2009-06-22 01:41:00','465');
INSERT INTO poct VALUES  (1234,DATE '2009-06-22 01:41:00','465');

 

[PHV]

What about this ?

SELECT P.inpatient_data_id, P.test_time, P.test_result, C.test_time, C.test_result, C.test_user_id
FROM (
SELECT inpatient_data_id, test_time, test_result
, (SELECT COUNT(*) FROM poct WHERE inpatient_data_id=A.inpatient_data_id AND test_time<=A.test_time) AS Rank
FROM poct A
) P LEFT JOIN (
SELECT inpatient_data_id, test_time, test_result, test_user_id
, (SELECT COUNT(*) FROM crit_care WHERE inpatient_data_id=A.inpatient_data_id AND test_time<=A.test_time) AS Rank
FROM crit_care A
) C ON P.inpatient_data_id=C.inpatient_data_id AND P.Rank=C.Rank
UNION ALL SELECT C.inpatient_data_id, P.test_time, P.test_result, C.test_time, C.test_result, C.test_user_id
FROM (
SELECT inpatient_data_id, test_time, test_result
, (SELECT COUNT(*) FROM poct WHERE inpatient_data_id=A.inpatient_data_id AND test_time<=A.test_time) AS Rank
FROM poct A
) P RIGHT JOIN (
SELECT inpatient_data_id, test_time, test_result, test_user_id
, (SELECT COUNT(*) FROM crit_care WHERE inpatient_data_id=A.inpatient_data_id AND test_time<=A.test_time) AS Rank
FROM crit_care A
) C ON P.inpatient_data_id=C.inpatient_data_id AND P.Rank=C.Rank
WHERE P.inpatient_data_id IS NULL
ORDER BY 1,2,5

I did an UNION but a single FULL OUTER JOIN should suffice:

SELECT COALESCE(P.inpatient_data_id,C.inpatient_data_id), P.test_time, P.test_result, C.test_time, C.test_result, C.test_user_id
FROM (
SELECT inpatient_data_id, test_time, test_result
, (SELECT COUNT(*) FROM poct WHERE inpatient_data_id=A.inpatient_data_id AND test_time<=A.test_time) AS Rank
FROM poct A
) P FULL OUTER JOIN (
SELECT inpatient_data_id, test_time, test_result, test_user_id
, (SELECT COUNT(*) FROM crit_care WHERE inpatient_data_id=A.inpatient_data_id AND test_time<=A.test_time) AS Rank
FROM crit_care A
) C ON P.inpatient_data_id=C.inpatient_data_id AND P.Rank=C.Rank
ORDER BY 1,2,5

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886