SQL Help

[Gman]

Trying to knock up a query to do the following:

I've got this table with the headings
Event Table
------------
ClientID EventNum StartDateTime EndDateTime SequenceID

T12000078 1	2007-01-02 10:00:00.000	2008-08-18 09:58:00.000	3220
T12000078 2	2008-08-15 10:37:00.000	2008-08-18 10:00:00.000	3233
T12000078 3	2008-03-18 10:04:00.000	2008-08-19 16:09:00.000	3235
T12000078 4	2008-08-19 10:09:00.000	2008-08-19 16:09:00.000	3236
T12000078 5	2008-06-05 16:15:00.000	2008-08-20 10:33:00.000	3254
T12000078 6	2008-08-20 10:35:00.000	2008-08-20 10:49:00.000	3259
T12000078 7	2008-08-20 10:48:00.000	NULL	                                3260
T12000078 8	2008-08-20 10:50:00.000	NULL	                                3261
T12000078 9	2008-08-20 10:50:00.000	NULL	                                3262
T12000078 10	2008-08-20 10:50:00.000	NULL	                                3263
T12000078 11	2008-08-20 19:55:00.000	NULL	                                3268

and theres another table with the following:
Treatment table
-------------------
ClientID Eventnum TreatStartDate Comment

T12000078	6		2008-08-20 10:38:00.000	Just a test

Now what i'm attempting to do is this, I need to work out if there are any entries before the TreatStartDate in the event table which have an end date which overlaps an entry which was recorded after the treatment startdate.

So for the above data the treatStartDate was recorded against eventNum 6 in the event table and the end date on this is 2008-08-20 10:49:00.000
this end date overlaps the startdate of eventNum 7 which is 2008-08-20 10:48:00.000. therefore the sql query should bring back just the sequence id 3260.

heres the query i'm playing with that doesn't seem to be quite working

SELECT SequenceID, StartDateTime
FROM dbo.MntClientSection mcs
WHERE mcs.StartDateTime > '2008-08-20 10:38:00.000' OR '2008-08-20 10:38:00.000' BETWEEN mcs.StartDateTime AND mcs.EndDateTime
AND mcs.StartDateTime < (SELECT max(mcs2.EndDateTime)
						   FROM dbo.MntClientSection mcs2
						   WHERE mcs2.EndDateTime < '2008-08-20 10:38:00.000' OR '2008-08-20 10:38:00.000' BETWEEN mcs2.StartDateTime AND mcs2.EndDateTime
						   AND mcs2.ClientID = 'T12000078'
						   AND mcs2.SequenceID <> mcs.SequenceID)
AND mcs.ClientID = 'T12000078'

that seems to be bringing back the following
3260
3261
3262
3263
3268

Any ideas ?

Cheers

 

[Gman]

Think I've cracked it, was missing some () off the OR statement in my sql so the working version is this:

SELECT SequenceID, startdatetime
FROM dbo.MntClientSection mcs
WHERE (mcs.StartDateTime > '2008-08-20 10:38:00.000' OR '2008-08-20 10:38:00.000' BETWEEN mcs.StartDateTime AND mcs.EndDateTime)
AND mcs.ClientID = 'T12000078'
AND mcs.StartDateTime < (SELECT max(mcs2.EndDateTime)
						   FROM dbo.MntClientSection mcs2
						   WHERE mcs2.EndDateTime < '2008-08-20 10:38:00.000' OR '2008-08-20 10:38:00.000' BETWEEN mcs2.StartDateTime AND mcs2.EndDateTime
						   AND mcs2.ClientID = 'T12000078'
						   AND mcs2.SequenceID <> mcs.SequenceID)