February 6, 2009 at 12:25 pm
:cool:I have a very complicated query that I need to write and am having trouble with the logic and am hoping anyone can help!!! I work in the IT dept for a fairly large hospital chain and they want me to write a report that tracks what patient users are searching for. The tools I have to work with are SQL 2005 and Crystal Reports 10.
I need to know who searched for a patient outside of their service area. Problem is that a user that performs the search can have one or more service areas attached to their user id. Same goes for the patient. A patient can have more than one service area.
The ACCESS_LOG table gives me 3 key fields. The Process_ID, User_ID, and Pat_ID. This table tells me what patient a user searched on. Table looks like this:
Process_IDUser_IDPat_ID
14033Gomer100
14034Gomer101
14035YoungC102
The PAT_SA table tells me what service areas are attached to each patient. It will look like this:
Pat_IDPAT_SA_ID
1002
1003
1004
1011
10220
10221
The USER_SA table is just like the PAT_SA except that is tells me what service areas are attached to each user. Looks like this:
User_IDUSER_SA_ID
Gomer20
Gomer2
Gomer1
YoungC2
YoungC3
YoungC1
The final report should only show what Users searched on a patient that were not in their service area. So let’s say I searched on a patient that only has service area 1 attached to them and I only have service area 2 and 3 attached to my user record that needs to show up on the report. Let’s say Patient has service area 20, 21, 1 and 2 attached to them and I have service area 3, 4 and 2 that should not show up on the report because the user and patient both have service area 2 attached to them.
Here is the query that I thought would work
SELECT USER_ID, PAT_ID, PROCESS_ID FROM ACCESS_LOG AS al WHERE USER_ID NOT IN
(SELECT DISTINCT USER_ID FROM USER_SA INNER JOIN PAT_SA ON USER_SA.USER_SA_ID = PAT_SA_ID WHERE USER_SA.USER_ID = al.user_id)
Any help would be greatly appreciated!!!!
February 6, 2009 at 3:04 pm
Please try:
SELECT [Process_ID]
,[User_ID]
,[Pat_ID]
FROM ACCESS_LOG
WHERE Process_ID NOT IN (SELECT Process_ID
FROM ACCESS_LOG a
INNER JOIN PAT_SA p
ON a.Pat_ID = p.Pat_ID
INNER JOIN USER_SA u
ON a.User_ID = u.User_ID
WHERE p.PAT_SA_ID = u.USER_SA_ID)
February 7, 2009 at 4:12 am
February 7, 2009 at 2:51 pm
This works like a charm. Thank you very much for the quick reply!!!!!!!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply