Help with query: including records

[mlee1steph]

Hi Everyone,
I have a query that returns clients that have attended the program. But I now need to update the query so that it does the above, but also clients that did not attend within the date range and that are activeclients. Would you mind a few pointers in the right direction. Here is the query I'm working with:

SELECT DISTINCT PATIENTS.PatientID, PATIENTS.LastName+', '+PATIENTS.FirstName AS P_NAME,
PATIENTS.MEDICAID_ID, COUNSELORS.C_LastName+', '+COUNSELORS.C_FirstName AS C_NAME,
TERMINATION.DOT, TERMINATION.DOA, ATTENDANCE.AttendDate, ATTENDANCE.SecurityTime,
ATTENDANCE.GroupTime, ATTENDANCE.COUNSELOR
FROM ((COUNSELORS INNER JOIN PATIENTS ON COUNSELORS.C_ID = PATIENTS.COUNSELOR)
INNER JOIN ATTENDANCE ON PATIENTS.PatientID = ATTENDANCE.PatientID)
INNER JOIN TERMINATION ON PATIENTS.PatientID = TERMINATION.PatientID
WHERE (((ATTENDANCE.AttendDate) Between '12/1/2006' And '12/30/2006')
AND ((PATIENTS.ActiveClient)=1))

Thanks for any help.
Michael lee

 

[SQLDenis]

this should do it (in theory)

SELECT DISTINCT PATIENTS.PatientID, PATIENTS.LastName+', '+PATIENTS.FirstName AS P_NAME, 
PATIENTS.MEDICAID_ID, COUNSELORS.C_LastName+', '+COUNSELORS.C_FirstName AS C_NAME, 
TERMINATION.DOT, TERMINATION.DOA, ATTENDANCE.AttendDate, ATTENDANCE.SecurityTime, 
ATTENDANCE.GroupTime, ATTENDANCE.COUNSELOR
FROM ((COUNSELORS INNER JOIN PATIENTS ON COUNSELORS.C_ID = PATIENTS.COUNSELOR) 
left JOIN ATTENDANCE ON PATIENTS.PatientID = ATTENDANCE.PatientID) 
INNER JOIN TERMINATION ON PATIENTS.PatientID = TERMINATION.PatientID
and (((ATTENDANCE.AttendDate) Between '12/1/2006'  And '12/30/2006') 
where ((PATIENTS.ActiveClient)=1))

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[mlee1steph]

Hi Denis,
Thanks for the reply. I played with that query and did not get the results I expected. there is (in my data) a client that has not been in the program at all in Dec and he does not show up in the results. I though about trying to union all the original query with one that would query everyone not added, but was also having problems with that idea. I'll continue working with your sample and see what I can come up with unless you have another suggestion. Thanks so much for your reply.
Michael Lee

 

[Catadmin]

The problem might be in the Attendance.AttendDate clause. Try something like:

WHERE (  (ATTENDANCE.AttendDate Between '12/1/2006'  And '12/30/2006')  
  OR (ATTENDANCE.AttendDate ISNULL) )
AND PATIENTS.ActiveClient = 1

BTW, you seem to be using an awful lot of unnecessary parens. I cut them out of the WHERE clause, so if you really need them, you can put them back. But if you really don't need them, the above WHERE clause should work.

Not tested, btw.




Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[SQLBill]

The attendDate is part of the JOIN, not the where. So it only affects the values being joined between Patients and Termination. Is that what you want?

And I agree with Catadmin, check your parenthesis...remove the ones between the *'s.

ON PATIENTS.PatientID = TERMINATION.PatientID
and *(*((ATTENDANCE.AttendDate) Between '12/1/2006' And '12/30/2006') --part of the ON so only affects this join

where *(*(PATIENTS.ActiveClient)=1)*)* --part of the whole statement

-SQLBill

Posting advice: FAQ481-4875

 

[mlee1steph]

Thats not what I wanted. The original query did work before they asked to include Active clients that have not attended within the date range. Now, an updated version of Denis query almost works :
SELECT DISTINCT PATIENTS.PatientID, PATIENTS.LastName+', '+PATIENTS.FirstName AS P_NAME,
PATIENTS.MEDICAID_ID, COUNSELORS.C_LastName+', '+COUNSELORS.C_FirstName AS C_NAME,
TERMINATION.DOT, TERMINATION.DOA, ATTENDANCE.AttendDate, ATTENDANCE.SecurityTime,
ATTENDANCE.GroupTime, ATTENDANCE.COUNSELOR
FROM ((COUNSELORS INNER JOIN PATIENTS ON COUNSELORS.C_ID = PATIENTS.COUNSELOR)
left JOIN ATTENDANCE ON PATIENTS.PatientID = ATTENDANCE.PatientID)
INNER JOIN TERMINATION ON PATIENTS.PatientID = TERMINATION.PatientID
where PATIENTS.ActiveClient=1 and
((ATTENDANCE.AttendDate Between '12/1/2006' And '12/30/2006')
OR (ATTENDANCE.AttendDate is null))
But the problem with this one, it seems to work if the client never attended, but say they attended several days in Nov, but have not been at all in Dec. If I where to run the query, this client is not showing up in the query results. I just need the client to show up in the results even if they did not attend within that date range. Thanks again for any help.
Michael

 

[SQLBill]

returns clients that have attended the program. But I now need to update the query so that it does the above, but also clients that did not attend within the date range and that are activeclients

I just noticed that you want:
1. all that DID attend the program (specific dates)
AND
2. anyone else who DID NOT attend but is active.

So, basically you want EVERYONE that was active during the specified dates. Is that correct?

-SQLBill

Posting advice: FAQ481-4875

 

[SQLBill]

Question....if they don't have an ATTENDANCE.AttendDate between those dates - HOW do you know they are active during that time frame? Do you have another column that shows when they were active from?

It would really help if you would provide sample data and what results you want to see.

-SQLBill

Posting advice: FAQ481-4875

 

[Catadmin]

Drop the ATTENDANCE.AttendDate is NULL from the modified query you have. Then try adding:

UNION ALL
SELECT DISTINCT PATIENTS.PatientID, PATIENTS.LastName+', '+PATIENTS.FirstName AS P_NAME, 
PATIENTS.MEDICAID_ID, COUNSELORS.C_LastName+', '+COUNSELORS.C_FirstName AS C_NAME, 
TERMINATION.DOT, TERMINATION.DOA, ATTENDANCE.AttendDate, ATTENDANCE.SecurityTime, 
ATTENDANCE.GroupTime, ATTENDANCE.COUNSELOR
FROM ((COUNSELORS INNER JOIN PATIENTS ON COUNSELORS.C_ID = PATIENTS.COUNSELOR) 
left JOIN ATTENDANCE ON PATIENTS.PatientID = ATTENDANCE.PatientID) 
INNER JOIN TERMINATION ON PATIENTS.PatientID = TERMINATION.PatientID
where PATIENTS.ActiveClient=1 and 
((ATTENDANCE.AttendDate <='12/1/2006'  
  And ATTENDANCE.AttendDate >='12/30/2006')
OR ATTENDANCE.AttendDate IS NULL)

This should get you the active clients who either attended before or after those dates or did not attend at all.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[mlee1steph]

Hi Everyone,

>>
So, basically you want EVERYONE that was active during the specified dates. Is that correct?
<<<

I guess it comes down to that.

>>>>Question....if they don't have an ATTENDANCE.AttendDate between those dates - HOW do you know they are active during that time frame? Do you have another column that shows when they were active from?
<<<<<

Yes, I have a field on the Patients table called ActiveClient that is set to 1 if the client is active. And Catadmin, I'll try your suggestion. Thanks for all the replys. I hope everyone had/Having a great holiday.
Thanks
Michael

 

[SQLBill]

Michael,

You say you have a column called ActiveClient that is set to 1 for TRUE. However, that only shows they are active NOW - not during the timeframe of the query.

It is now December. So new Client Joe is active during this month and his ActiveClient value is 1. Now you query for the month of November, Joe will show up even though he wasn't active during November - he wasn't even a client then. How do you handle that? In other words, how do you determine WHEN a client was/is active besides the current moment?

-SQLBill

Posting advice: FAQ481-4875

 

[mlee1steph]

Hi SQLBill,
Thanks for the reply. You actually have a point. I will have to setup for that. We have a termination table thats linked to the Patients table (PatientId is Key). It contains two fields:
DOA (Date of Admission)
DOT (Date of Termination)
When they first add or admitt a client, the DOA field is updated with the new admission date. When the client is terminated, the DOT field is filled in and the Activeclient flag is changed to 0 (False).
Thanks again for the reply. I will have to take another look at the query and see what I can do about the case you mentioned. Thanks again.
Michael

 

[SQLBill]

Q: Can the DOT ever be entered for a future date? For example, you know client John Doe is terminating on 31 December 2006. Can his termination date be entered today (27 December 2006)? If so, his ActiveClient column will show he's not active, even though he still is active.

You will want something like:

WHERE ActiveClient = 1
AND (DOA >= 'startdate'
AND DOT <= 'enddate')

That should pull all of your client's that were active between the start and end dates, which is what you appear to be asking for. Of course, I didn't include the table names or joins.....

-SQLBill


Posting advice: FAQ481-4875