Grouping partially correlated data

Database
Enthusiast

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
Senior Apprentice

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
Enthusiast

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