Finding date clashes

If I had a table of holidayItems (a holiday is made up of one or more
holidayItems) that contains the columns, Id, HolidayID, StartDate EndDate,
and I wanted to write a sproc that returned a table of clashes i.e the
Holiday ID,the Id of the holiday it clashed with and the dates the holidays
clash. How would I go about it? There will be no clashes of holiday item
dates within a holiday. I am looking for clashes between different holidays.
Sample data:
ID HolidayID StartDate EndDate
1 1 08/05/05 08/10/05
2 1 08/13/05 08/15/05
3 2 08/01/05 08/05/05
4 3 08/02/05 08/04/05
5 4 08/13/05 08/20/05
6 4 08/23/05 08/25/05
Result set
HolidayID ClashesWithHolidayID DateClash
1 2 08/05/05
1 4 08/13/05
1 4 08/14/05
1 4 08/15/05
2 3 08/02/05
2 3 08/03/05
2 3 08/04/05
Table script
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[HolidayItem]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[HolidayItem]
GO
CREATE TABLE [dbo].[HolidayItem] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[HolidayID] [int] NOT NULL ,
[StartDate] [smalldatetime] NOT NULL ,
[EndDate] [smalldatetime] NOT NULL
) ON [PRIMARY]
GOSELECT H1.id, H2.id,
H1.startdate, H1.enddate, H2.startdate, H2.enddate
FROM HolidayItem AS H1
JOIN HolidayItem AS H2
ON (H1.startdate <= H2.enddate
AND H1.enddate >= H2.startdate)
AND H1.id < H2.id ;
To get the individual dates, just join to a calendar table on BETWEEN
startdate AND enddate.
Don't forget to add the important constraints. I don't quite understand
why your sample data has different start and end dates for the same
"holidayid" but however that may be, startdate ought to be part of a
composite unique key. Also, add the check constraint startdate <=
enddate
David Portas
SQL Server MVP
--
David Portas
SQL Server MVP
--|||The reason for the two start and end dates for the same holidayid is that it
is a table of holiday items and a holiday can have one or more items making
up the holiday. The reason for this is so that i do not record wends in
the holiday period.
Thanks very much for your help
john
"David Portas" wrote:

> SELECT H1.id, H2.id,
> H1.startdate, H1.enddate, H2.startdate, H2.enddate
> FROM HolidayItem AS H1
> JOIN HolidayItem AS H2
> ON (H1.startdate <= H2.enddate
> AND H1.enddate >= H2.startdate)
> AND H1.id < H2.id ;
> To get the individual dates, just join to a calendar table on BETWEEN
> startdate AND enddate.
> Don't forget to add the important constraints. I don't quite understand
> why your sample data has different start and end dates for the same
> "holidayid" but however that may be, startdate ought to be part of a
> composite unique key. Also, add the check constraint startdate <=
> enddate
> --
> David Portas
> SQL Server MVP
> --
>
> --
> David Portas
> SQL Server MVP
> --
>|||Hi Portas
does your query cover if start date starts after 2nd startdate and ends
before second end date.(intervening period_
similary of start date starts before 2nd startdate and ends after 2end end
dates(covering period)
and such scenarios
This union query may be used but not tested
SELECT dbo.HolidayItem.ID, dbo.HolidayItem.HolidayID,
dbo.HolidayItem.StartDate, dbo.HolidayItem.EndDate, HolidayItem_1.HolidayID
AS ClashID
FROM dbo.HolidayItem INNER JOIN
dbo.HolidayItem HolidayItem_1 ON
dbo.HolidayItem.StartDate >= HolidayItem_1.StartDate AND
dbo.HolidayItem.EndDate <= HolidayItem_1.EndDate AND
dbo.HolidayItem.HolidayID <> HolidayItem_1.HolidayID
union
SELECT dbo.HolidayItem.ID, dbo.HolidayItem.HolidayID,
dbo.HolidayItem.StartDate, dbo.HolidayItem.EndDate, HolidayItem_1.HolidayID
AS ClashID
FROM dbo.HolidayItem INNER JOIN
dbo.HolidayItem HolidayItem_1 ON
dbo.HolidayItem.HolidayID <> HolidayItem_1.HolidayID AND
dbo.HolidayItem.StartDate <= HolidayItem_1.StartDate
AND dbo.HolidayItem.StartDate >= HolidayItem_1.EndDate
union
SELECT dbo.HolidayItem.ID, dbo.HolidayItem.HolidayID,
dbo.HolidayItem.StartDate, dbo.HolidayItem.EndDate, HolidayItem_1.HolidayID
AS ClashID
FROM dbo.HolidayItem INNER JOIN
dbo.HolidayItem HolidayItem_1 ON
dbo.HolidayItem.HolidayID <> HolidayItem_1.HolidayID AND
dbo.HolidayItem.StartDate <= HolidayItem_1.StartDate
AND dbo.HolidayItem.StartDate <= HolidayItem_1.EndDate
union
SELECT dbo.HolidayItem.ID, dbo.HolidayItem.HolidayID,
dbo.HolidayItem.StartDate, dbo.HolidayItem.EndDate, HolidayItem_1.HolidayID
AS ClashID
FROM dbo.HolidayItem INNER JOIN
dbo.HolidayItem HolidayItem_1 ON
dbo.HolidayItem.HolidayID <> HolidayItem_1.HolidayID AND
dbo.HolidayItem.StartDate <= HolidayItem_1.StartDate
AND dbo.HolidayItem.StartDate >= HolidayItem_1.EndDate
union
SELECT dbo.HolidayItem.ID, dbo.HolidayItem.HolidayID,
dbo.HolidayItem.StartDate, dbo.HolidayItem.EndDate, HolidayItem_1.HolidayID
AS ClashID
FROM dbo.HolidayItem INNER JOIN
dbo.HolidayItem HolidayItem_1 ON
dbo.HolidayItem.HolidayID <> HolidayItem_1.HolidayID AND
dbo.HolidayItem.EndDate >= HolidayItem_1.StartDate AND
dbo.HolidayItem.EndDate <= HolidayItem_1.EndDate
r.d
"John" wrote:
> The reason for the two start and end dates for the same holidayid is that
it
> is a table of holiday items and a holiday can have one or more items makin
g
> up the holiday. The reason for this is so that i do not record wends in
> the holiday period.
> Thanks very much for your help
> john
> "David Portas" wrote:
>|||> does your query cover if start date starts after 2nd startdate and ends
> before second end date
Yes.
Compare your results to mine. There is a difference and I think some of
your joins need looking at. The following one looks wrong for a start
but your approach is basically sound, although a bit more typing than
mine!
dbo.HolidayItem.StartDate <= HolidayItem_1.StartDate
AND dbo.HolidayItem.StartDate <= HolidayItem_1.EndDate
David Portas
SQL Server MVP
--|||Your DDL had no key, you formatted the dates wrong, used IDENTITY and
lacked constraints. Is this what you really meant?
CREATE TABLE HolidaySchedules
(holiday_item INTEGER NOT NULL
start_date DATETIME NOT NULL
CHECK( << set to 00:00:00 Hrs>> ),
end_date DATETIME NOT NULL
CHECK( << set to 23:59:59.9999.. Hrs>> ),
CHECK (start_date < end_date),
PRIMARY KEY (holiday_item, start_date)); -- real key!!
INSERT INTO HolidaySchedules VALUES (1, '2005-08-05', '2005-08-10');
INSERT INTO HolidaySchedules VALUES (1, '2005-08-13', '2005-08-15');
INSERT INTO HolidaySchedules VALUES (2, '2005-08-01', '2005-08-05');
INSERT INTO HolidaySchedules VALUES (3, '2005-08-02', '2005-08-04');
INSERT INTO HolidaySchedules VALUES (4, '2005-08-13', '2005-08-20');
INSERT INTO HolidaySchedules VALUES (4, '2005-08-23', '2005-08-25');
s between different holidays. <<
Create a Calendar table with all the temporal data you use in your
enterprise. It will hlep with this query and a lot of others. First a
warm up query in a VIEW.
CREATE VIEW BadDates(cal_date, conflict_count)
AS
SELECT C.cal_date, COUNT(*)
FROM Calendar AS C1, HolidaySchedules AS H
WHERE C.cal_date BETWEEN H.start_date AND H.end_date
GROUP BY C.cal_date
HAVING COUNT(*) > 1;
This gives us rhe dates with conflicts. You can use those dates in the
desired query. Obviously, the VIEW can be made into a derived table or
CTE.
SELECT B.cal_date, H.holiday_item
FROM HolidaySchedules AS H, BadDates AS B
WHERE B.cal_date BETWEEN H.start_date AND H.end_date;
If you want to generate pairs of H.holiday_items, put this into another
VIEW and do a self-join on cal_dates. But I would do that in the front
end and not the database.|||yes, there are some joins where we need to add some more 'AND' OPERATOR.
FOR EX:
a date may start before second start date but ends before second end dates.
in this case we have to add one more condition : the first end date is
greater than second start date.
there is another scenario similar to this: it starts after first startdate
and ends after second end date.
bye
devaraj
"David Portas" wrote:

> SELECT H1.id, H2.id,
> H1.startdate, H1.enddate, H2.startdate, H2.enddate
> FROM HolidayItem AS H1
> JOIN HolidayItem AS H2
> ON (H1.startdate <= H2.enddate
> AND H1.enddate >= H2.startdate)
> AND H1.id < H2.id ;
> To get the individual dates, just join to a calendar table on BETWEEN
> startdate AND enddate.
> Don't forget to add the important constraints. I don't quite understand
> why your sample data has different start and end dates for the same
> "holidayid" but however that may be, startdate ought to be part of a
> composite unique key. Also, add the check constraint startdate <=
> enddate
> --
> David Portas
> SQL Server MVP
> --
>
> --
> David Portas
> SQL Server MVP
> --
>sql