Grouping partially correlated data
Grouping partially correlated data
(OP)
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
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
RE: Grouping partially correlated data
CODE
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');
RE: Grouping partially correlated data
CODE
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:
CODE
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: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?