SQL Query Help

[Gman]

i've got a temp table which contains the following

EventNum | StartDateTime | EndDateTime | SequenceID
------------------------------------------------------------
1 03/08/08 06/08/08 3074
2 05/08/08 08/08/08 3075
3 07/08/08 11/08/08 3076
4 09/08/08 Null 3077


Now in another table (lets call it Table2) i've got a start date which will fall between one of the dates in this table i.e. 06/08/08 will will between the startdatetime and endatetime of EventNum 2

as you can see the EndDateTime often overlaps the following StartDateTime indicating that the following entry was created before the previous one was closed, therefore this shows it belongs to the same collection.

If there were no consecutive overlapping entries then it would indicate a new collection.

what I'm trying to do is get the curent date out of 'Table2' and check to see if this belongs to CURRENT collection.

any sql gurus arround to lend a helping hand ?

Cheers

 

[oneilldo]

Not sure if I fully understand what you are trying to achieve.
This assumes that the last (most recent entry) in table1 is the current entry and always has an endatetime of NULL.
You'll need to change the count(1) to what fields you want to select
Does table 2 only contain one value?


SELECT count(1)
FROM Table1 t1, Table2 t2
WHERE t1.EndDateTime IS NULL
AND t1.StartDateTime <= t2.DateTime



If the enddatetime of the most recent entry in table 1 can be a non null value then the following might help (though this assumes that the current event always starts after another event)

SELECT count(1)
FROM Table1 t1, Table2 t2
WHERE t1.EndDateTime >= t2.DateTime
AND t1.StartDateTime <= t2.DateTime
AND NOT EXISTS (SELECT 1 FROM Table1 t3
WHERE t3.StartDateTime > t1.StartDateTime
AND t3.EventNum<> t1.EventNum)

If its the current datetime you are needing then GETDATE() (SQl Server) might help?

 

[Gman]

thanks for the effort mate, passed this issue onto someone else now as i'm on my holls now lol