Find if the specified date is in the list of date ranges
I have a list of events that need to be processed on an ongoing basis. The process takes care of the event for some time, after which it needs to be processed again. This means that for each event, I get a list of the date ranges in which the event was "cared for", and any spaces mean that the event was in an unhandled state at the time. I am using two tables for this:
Table Processes
ProcessID int PK NOT NULL
EffectiveDays int NOT NULL
Processing tables
EventID nvarchar(128) NOT NULL
ProcessID int FK REFERENCES Processes NOT NULL
DateProcessed int NOT NULL
The EventID is dynamically generated in a standard way based on the results of the SQL query to ensure that it is constant across all runs.
I would like to be able to implement a method with this signature (it sometimes needs to be called in a loop):
bool Event.WasProcessed(DateTime AsOf)
I need to be able to query for a specific date that states that the event has been (handled or not). So I will need to find if the specified date is in any of the date ranges (which may overlap) or not.
I'm not even sure if I should run this query in SQL or code. Sometimes a query needs to be run in an arbitrary sequence of events (each of which I represent to an object that stores an event and a list of processes), so running it in SQL may mean very little hitting the database. On the other hand, SQL is usually faster on queries. On the other hand, this similar problem shows that a cursor-based solution is needed anyway.
Any pointers will be appreciated, even if they are only the best search strings. (Googling any daterange question seems to require some skill with this.)
Request event status for any date:
SELECT TOP 1 eventState
FROM processing
WHERE eventId = @id
AND processingDate <= GETDATE()
ORDER BY
processingDate DESC
Update:
I am assuming the event has a value processed
on the given date if the date is in the range EffectiveDays
of 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
source to share