<p>An index imposes a cost when the underlying table is updated. An index provides a benefit when it is used to spped up a query. For each index, you need to balance the cost against the benefit. How much slower does the query run without the index? How much of a benefit is running faster? Can you or your users tolerate the slow speed when the index is missing?</p> Comparing date ranges

I made some simple tests on my real project and real MySql database. I already answered in this topic: What is the cost of indexing multiple db columns?

For your final NOT IN LIST example, you can see that it matches those two rules.

But I think it will be better if I quote it here:

You will need to decide wether the following periods are IN or OUTSIDE your ranges:

           |-------------|
|-------|                       equal end with start of comparison period
|-----|   equal start with end of comparison period

I made some simple tests using my real

If your table has columns called range_end and range_start, here's some simple SQL to retrieve all the matching rows:

SELECT *
FROM periods
WHERE NOT (range_start > @check_period_end
OR range_end < @check_period_start)

project and real MySql database.

My results are: adding average index (1-3 columns in an index) to a table - makes inserts slower by 2.1%. So, if you add 20 indexes, your inserts will

Note the NOT in there. Since the two simple rules finds all the non-matching rows, a simple NOT will reverse it to say: if it's not one of the non-matching rows, it has to be one of the matching ones.

be slower by 40-50%. But your selects

Applying simple reversal logic here to get rid of the NOT and you'll end up with:

SELECT *
FROM periods
WHERE range_start <= @check_period_end
AND range_end >= @check_period_start

Sql server gives you some good tools that let you see which indexes are actually being used.

i.e. check the start of your test range is before the end of the database range, and that the end of your test range is after or on the start of the database range.

If your RDBMS supports the OVERLAP() function then this becomes trivial -- no need for homegrown solutions. (In Oracle it apparantly works but is undocumented).

06/06/1983 to 18/06/1983 = IN LIST

It is totally based on the columns which are being used in Where Clause.

However, this period does not contain nor is contained by any of the periods in your table (not list!) of periods. It does, however, overlap the period 10/06/1983 to 14/06/1983.

You may find the Snodgrass book (http://www.cs.arizona.edu/people/rts/tdbbook.pdf) useful: it pre-dates mysql but the concept of time hasn't changed ;-)

I created function to deal with this problem in MySQL. Just convert the dates to seconds before use.

DELIMITER ;;


CREATE FUNCTION overlap_interval(x INT,y INT,a INT,b INT)
RETURNS INTEGER DETERMINISTIC
BEGIN
DECLARE
overlap_amount INTEGER;
IF (((x <= a) AND (a < y)) OR ((x < b) AND (b <= y)) OR (a < x AND y < b)) THEN
IF (x < a) THEN
IF (y < b) THEN
SET overlap_amount = y - a;
ELSE
SET overlap_amount = b - a;
END IF;
ELSE
IF (y < b) THEN
SET overlap_amount = y - x;
ELSE
SET overlap_amount = b - x;
END IF;
END IF;
ELSE
SET overlap_amount = 0;
END IF;
RETURN overlap_amount;
END ;;


DELIMITER ;

Look into the following example. It will helpful for you.

    SELECT  DISTINCT RelatedTo,CAST(NotificationContent as nvarchar(max)) as NotificationContent,
ID,
Url,
NotificationPrefix,
NotificationDate
FROM NotificationMaster as nfm
inner join NotificationSettingsSubscriptionLog as nfl on nfm.NotificationDate between nfl.LastSubscribedDate and isnull(nfl.LastUnSubscribedDate,GETDATE())
where ID not in(SELECT NotificationID from removednotificationsmaster where Userid=@userid) and  nfl.UserId = @userid and nfl.RelatedSettingColumn = RelatedTo

CREATE FUNCTION overlap_date(s DATE, e DATE, a DATE, b DATE)
RETURNS BOOLEAN DETERMINISTIC
RETURN s BETWEEN a AND b or e BETWEEN a and b or  a BETWEEN s and e;

Try This on MS SQL

WITH date_range (calc_date) AS (
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, [ending date]) - DATEDIFF(DAY, [start date], [ending date]), 0)
UNION ALL SELECT DATEADD(DAY, 1, calc_date)
FROM date_range
WHERE DATEADD(DAY, 1, calc_date) <= [ending date])
SELECT  P.[fieldstartdate], P.[fieldenddate]
FROM date_range R JOIN [yourBaseTable] P on Convert(date, R.calc_date) BETWEEN convert(date, P.[fieldstartdate]) and convert(date, P.[fieldenddate])
GROUP BY  P.[fieldstartdate],  P.[fieldenddate];

Another method by using BETWEEN sql statement

Periods included :

SELECT *
FROM periods
WHERE @check_period_start BETWEEN range_start AND range_end
AND @check_period_end BETWEEN range_start AND range_end

Periods excluded :

SELECT *
FROM periods
WHERE (@check_period_start NOT BETWEEN range_start AND range_end
OR @check_period_end NOT BETWEEN range_start AND range_end)

SELECT *
FROM tabla a
WHERE ( @Fini <= a.dFechaFin AND @Ffin >= a.dFechaIni )
AND ( (@Fini >= a.dFechaIni AND @Ffin <= a.dFechaFin) OR (@Fini >= a.dFechaIni AND @Ffin >= a.dFechaFin) OR (a.dFechaIni>=@Fini AND a.dFechaFin <=@Ffin) OR
(a.dFechaIni>=@Fini AND a.dFechaFin >=@Ffin) )