Find if a given date is in a list of date ranges

Question

I have a list of events that need processing on a constant basis. The process takes care of the event for some time, whereupon it needs processing again. This means that I end up, for each event, with a list of date ranges in which the event was "taken care of" and any gaps mean the event was in an unprocessed state at that time. For this I use two tables:

Table Processes

ProcessID int PK NOT NULL  
EffectiveDays int NOT NULL

Table Processings

EventID nvarchar(128) NOT NULL  
ProcessID int FK REFERENCES Processes NOT NULL  
DateProcessed int NOT NULL

EventID is dynamically generated in a standard way based on the results of a SQL query to ensure that it is constant across runs.

I would like to able to implement a method with this signature (it will need to sometimes be called in a loop):

bool Event.WasProcessed(DateTime AsOf)

I need to be able to query, for a given date, which state the event was in (processed or not). So I would need to find if the date supplied is in any one of the date ranges (which can overlap) or not.

I'm not even sure whether to run this query in SQL or code. The query will sometimes need to be run on an arbitrary series of events (each of which I represent by an object that hold the event and a list of processings), so running it in SQL could mean hitting the DB quite a bit. On the ther hand SQL is generally faster at queries. On the yet other hand this similar issue shows that a cursor based solution would anyway be needed.

Any pointers would be appreciated, even if they are only better search strings. (Googling any daterange issue seems to requires some skill with that.)

Answer 1

To query the state of event for any given date:

SELECT  TOP 1 eventState
FROM    processing
WHERE   eventId = @id
        AND processingDate <= GETDATE()
ORDER BY
        processingDate DESC

Update:

I assume an event is processed at a given date if the date is within EffectiveDays from DateProcessed.

SELECT  p.ProcessID
FROM    Processings pi
LEFT JOIN
        Processes p
ON      p.ProcessID = pi.ProcessID
        AND p.EffectiveDays >= @date - pi.DateProcessed
WHERE   pi.EventID = @EventID
        AND pi.DateProcessed <= @date