June 30, 2011 at 3:24 pm
I have two tables with identical structures holding information about meetings tbPropMeeting, tbExistMeetings. The fields (all datetime fields) are dtMeeting, dtHRStart and dtHRFinish.
I am trying to find records in tbPropMeeting that match any record in tbExistMeetings and also overlap in time. In other words:
tbPropMeeting.dtMeeting = tbExistMeetings.dtMeeting AND
(tbPropMeeting.dtHRStart between tbExistMeetings.dtHRStart and tbExistMeetings.dtHRFinish
or
tbPropMeeting.dtHRFinish between tbExistMeetings.dtHRStart and tbExistMeetings.dtHRFinish
or
tbExistMeetings.dtHRStart between tbPropMeeting.dtHRStart and tbPropMeeting.dtHRFinish
or
tbExistMeetings.dtHRFinish between tbPropMeeting.dtHRStart and tbPropMeeting.dtHRFinish)
Clearly the above block isn't quite right, but the time clauses take care of all situations where there is overlap in time. I need to somehow work this together into a query that applies these time matches at the same time as matching the dates as well. Can someone suggest a structure that I can use to achieve this?
July 1, 2011 at 7:44 am
I've just re-read this and think perhaps it isn't very clear. I want to return records from tableA where the date matches tableB AND the time period for the matching records overlap. I can't believe there isn't a way. Surely I don't have to loop through a cursor?
July 1, 2011 at 8:23 am
It may help us if you provided some sample DDL and DML so we can build a little test environment on our side. This way, with the sample data and your expected results we'll have a better idea of what you're after. This article is a nice guide towards what I am asking for:
http://www.sqlservercentral.com/articles/Best+Practices/61537/%5B/url%5D
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply