Grouping partially correlated data

Database

Grouping partially correlated data

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? Code to create and populate example tables is:


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


Thanks,
Kevin
2 REPLIES
N/A

Re: Grouping partially correlated data

Hi Kevin,
to me it seems a very strange report, are you shure its not confusing?

It's easy to get using OLAP functions:
SELECT
COALESCE(t1.inpatient_data_id,t2.inpatient_data_id),
t1.test_time,
t1.test_result,
t2.test_type,
t2.test_time,
t2.test_result,
t2.test_user_id
FROM
(
SELECT
inpatient_data_id,
test_time,
test_result,
ROW_NUMBER() OVER (PARTITION BY inpatient_data_id ORDER BY test_time) AS rnk
FROM poct
) AS t1
FULL OUTER JOIN
(
SELECT
inpatient_data_id,
test_type,
test_time,
test_result,
test_user_id,
ROW_NUMBER() OVER (PARTITION BY inpatient_data_id ORDER BY test_time) AS rnk
FROM crit_care
) AS t2
ON t1.inpatient_data_id = t2.inpatient_data_id
AND t1.rnk = t2.rnk

Btw, your CREATEs don't run, this is Teradata not MS SQL Server, thus it's TIMESTAMP(0) instead of DateTime :-)

Dieter

Re: Grouping partially correlated data

Hi Dieter,

Thanks for the code. I had thought about something like this, but couldn't quite figure out how to code it.

It does look confusing in text format, but these are the requirements I have. I think if presented correctly in Crystal Reports it will be useful.

Thanks for pointing out the error in the CREATE. I also posted this on a SQL Server site and didn't get all my syntax reset.

Thanks,
Kevin