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_result1234 2009-06-22 01:41:00 4651234 2009-06-22 02:41:00 High1234 2009-06-22 03:41:00 4553456 2009-06-14 13:26:00 173456 2009-06-14 15:26:00 385678 2009-07-03 12:12:00 High5678 2009-07-03 13:12:00 High5678 2009-07-03 14:12:00 483If 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_id1234 glucose 2009-06-22 01:45:00 465 111113456 glucose 2009-06-14 13:26:00 22 222223456 glucose 2009-06-14 13:28:00 20 222223456 glucose-POCT 2009-06-14 15:28:00 40 333333456 glucose-POCT 2009-06-14 16:28:00 37 33333I 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_id1234 2009-06-22 01:41:00 465 glucose 2009-06-22 01:45:00 465 111111234 2009-06-22 02:41:00 High NULL NULL NULL NULL1234 2009-06-22 03:41:00 455 NULL NULL NULL NULL3456 2009-06-14 13:26:00 17 glucose 2009-06-14 13:26:00 22 222223456 2009-06-14 15:26:00 38 glucose 2009-06-14 13:28:00 20 222223456 NULL NULL glucose-POCT 2009-06-14 15:28:00 40 333333456 NULL NULL glucose-POCT 2009-06-14 16:28:00 37 333335678 2009-07-03 12:12:00 High NULL NULL NULL NULL NULL5678 2009-07-03 13:12:00 High NULL NULL NULL NULL NULL5678 2009-07-03 14:12:00 483 NULL NULL NULL NULL NULLAny suggestions for SQL to return this result set? While I realize this is a t-SQL forum, more generic code would be helpful. 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
Grouping partially correlated data
Posted - 2009-08-04 : 15:50:57
