Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
VicM
Starting Member
2 Posts |
 Posted - 2009-09-15 : 12:51:20
|
I need to determine when a patient's appointment for therapy took place during times a doctor was NOT in house. Therapies are often performed by qualified non-doctor personnel.I have a table, PtAppts, of patient appointments listing the service date, appointment time and length of session. The dates are all stored at 0:00 hrs on the day of service and the times are stored in a separate field as 1753-01-01 12:30:00.00, for example. Using the length of time in minutes for the session, I calculate an ApptEndTime column by adding the length to the appointment time.I also have a table, DrIn, that lists the dates, beginning and ending times a doctor is in house. The dates and times are in the same format as the first table.So in order to determine when a patient's appointment did NOT coincide with a doctor's presence, I have the following code: SELECT P.* FROM PtAppts AS P INNER JOIN DrIn AS D ON P.ApptDate = D.ApptDate WHERE NOT (P.ApptTime BETWEEN D.DrBegTime AND D.DrEndTime) AND NOT (P.ApptEndTime BETWEEN D.DrBegTime AND D.DrEndTime) I'm running into a problem when more than one doctor is in house and there are two (or more) records in the DrIn table. One of the records encompasses the beginning or end time of the patient's appointment, but the other does not. The resultant list then shows that patient's appointment met the criteria in the WHERE clause and indicates that a doctor was NOT in house during any part of that appointment. Also, it did not change the results when I changed the order in the DrIn table of the two records. (I was able to do so because I initially ordered the results solely by date which listed the later times first. I then ordered the table by date and DrBegTime which interchanged the two records.)Any suggestions/recommendations on how to solve this problem?Thanks,Vic |
|
|
|
|
|
|
|
Datetime query not performing as expected