Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Grouping partially correlated data

Status
Not open for further replies.
kdbenson

kdbenson

Programmer
Joined
Jan 7, 2009
Messages
11
Location
US
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
 
Sort by date Sort by votes
Code to create and populate example tables (using Teradata) is:

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');
 
Upvote 0 Downvote
What about this ?
Code: 
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:
Code: 
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

mmiah1982
  • Locked
  • Question Question
Extracting mutiple data items from DB with JSON string format column
Replies
2
Views
563
mmiah1982
mmiah1982
fredong
  • Locked
  • Question Question
Update upper row data with next row data that was detached partially from import.
Replies
6
Views
828
carp
carp
Junior6202
  • Locked
  • Question Question
String or binary data would be truncated.
Replies
1
Views
542
fredericofonseca
fredericofonseca
kdbenson
  • Locked
  • Question Question
Match unlinked data based on datetimes
Replies
1
Views
340
JarlH
JarlH
soushi01
  • Locked
  • Question Question
Display only certain year data [SYSDATE]
Replies
2
Views
318
PHV
PHV

Part and Inventory Search

Sponsor

Back
Top