Date Overlap Algorithm

Hi all,
I'm looking for an efficient algorithm that will aid us in date overlap
comparisons. Everything we've come up with so far is not reasonble
performance wise.
Here is the scenario, I'll keep this as generic as possible:
Given the following table:
MasterID (numeric), ItemID (text), Date1, Date2
1, ItemA, Jan 1, Jan 20
2, ItemB, Jan 1, Jan 20
3, ItemA, Jan 21, Jan 30
4, ItemA, Jan 15, Jan 25
Consider that this table is very large and in a given date range an item may
appear dozens of times.
Date 1 and Date 2 represent an "on/off" switch so to speak. Date1 turns an
item On, date2 turns it Off.
The scenario is that under normal circumstances ItemID has a date range
identifying when the switch is on with no overlap in the dates. However
another valid scenario is where there IS overlap in dates for a given Item.
Now we have a report where we need to display how many items are "on" for a
given month range, but we require daily resolution.
In the above sample table, date overlap isn't considered and the results are
Jan 1 - 14 = 2 items
Jan 15 - 20 = 4 items
Jan 21 - 25 = 2 items
Jan 26 - 30 = 1 item
However it should read
Jan 1 - 20 = 2 items
Jan 21 - 30 = 1 item
as no item should be counted more than once for a given date range.
We have no particular output requirements in that we're flexible and just
need to be able to efficiently identify these duplicates with some
Any thoughts would be appreciated.Create a Calendar table with dates. Let me provide some DDL that you
did not:
CREATE TABLE Events
(event_name CHAR(15) NOT NULL PRIMARY KEY,
start_time DATETIME NOT NULL,
end_time DATETIME, -- null is current
CHECK (start_time < end_time));
Now you can get the distinct event tally per day:
SELECT C1.cal_date, COUNT(DISTINCT event_name) AS tally
FROM Calendar AS C1, Events AS E1
WHERE C1.cal_date BETWEEN E1.start_time AND E1.end_time
GROUP BY C1.cal_date
HAVING COUNT(DISTINCT event_name) > 1;
Now you need to put this in a view and build another tblke with month
ranges to get your report.