×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Grouping partially correlated data

thread220-1562628

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

RE: Grouping partially correlated data

(OP)
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');

RE: Grouping partially correlated data

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: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close