September 25, 2007 at 11:27 am
this article http://sqlnerd.blogspot.com/2005/06/t-sql-speed-efficiencies-with-double_30.html on event overlap is very valuable. it's not easy to find much on the topic.
I tried to adapt the double-negative idea to a related problem: retreiving only the latest version of a meeting for each event - that is get all non-overlapping meetings for a location, and the latest overlapping meeting for a location (discarding the previously entered overlaps).
----
CREATE TABLE SBSSM.EventSegment
(
EventSegment_ID int IDENTITY (1, 1) NOT NULL,
StartTime datetime NOT NULL,
EndTime datetime NOT NULL,
Activity_ID int NOT NULL,
Location_code nvarchar(20) NOT NULL,
[DBTimeStamp] [timestamp] NULL,
CONSTRAINT IUC220 PRIMARY KEY(EventSegment_ID),
constraint ETGTST check (EndTime > StartTime)
)
GO
--test data
insert into sbssm.eventsegment (StartTime, EndTime, Activity_ID, Location_code) values
('2007-09-24 17:00','2007-09-24 18:00',1,'PL')
insert into sbssm.eventsegment (StartTime, EndTime, Activity_ID, Location_code) values
('2007-09-24 16:00','2007-09-24 17:00',1,'PL')
insert into sbssm.eventsegment (StartTime, EndTime, Activity_ID, Location_code) values
('2007-09-24 16:00','2007-09-24 17:00',1,'PL')
insert into sbssm.eventsegment (StartTime, EndTime, Activity_ID, Location_code) values
('2007-09-24 17:00','2007-09-24 18:00',1,'PL')
insert into sbssm.eventsegment (StartTime, EndTime, Activity_ID, Location_code) values
('2007-09-24 16:30','2007-09-24 17:30',1,'PL')
insert into sbssm.eventsegment (StartTime, EndTime, Activity_ID, Location_code) values
('2007-09-24 15:00','2007-09-24 16:00',1,'PL')
insert into sbssm.eventsegment (StartTime, EndTime, Activity_ID, Location_code) values
('2007-09-24 15:00','2007-09-24 16:00',1,'PL2')
insert into sbssm.eventsegment (StartTime, EndTime, Activity_ID, Location_code) values
('2007-09-24 15:00','2007-09-24 16:00',2,'PL')
GO
Select * from sbssm.EventSegment
-- EventSegment_ID 5,7,8 are the ones needed
GO
--my having clause causes yack: problem is references to joined table. How do i get around this?
select max(es.EventSegment_ID) esID, es.Activity_ID, es.StartTime, es.Endtime, es.Location_code
from sbssm.EventSegment es
join sbssm.EventSegment es2
on es.Location_code = es2.Location_code
and es.Activity_ID = es2.Activity_ID
and (es.starttime >= es2.endtime or es.endtime <= es2.starttime)
Group by es.Activity_ID, es.StartTime, es.Endtime, es.Location_code
having (es.starttime >= es2.endtime or es.endtime <= es2.starttime)
GO
/*>
Msg 8121, Level 16, State 1, Line 2
Column 'sbssm.EventSegment.EndTime' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8121, Level 16, State 1, Line 2
Column 'sbssm.EventSegment.StartTime' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
>*/
---
I'm getting severe brain damage on this one. if you can see what's wrong, you're not only helping me, you're helping the SQL world! TKS
September 25, 2007 at 1:45 pm
--add a couple more test records
insert into sbssm.eventsegment (StartTime, EndTime, Activity_ID, Location_code) values
('2007-09-24 15:00','2007-09-24 16:00',2,'PL3')
insert into sbssm.eventsegment (StartTime, EndTime, Activity_ID, Location_code) values
('2007-09-24 18:00','2007-09-24 19:00',2,'PL')
GO
-- EUREKA! this seems to do the trick:
Select a.* from sbssm.EventSegment a
join (
select Max(es.EventSegment_ID)EventSegment_ID, es.Activity_ID
from sbssm.EventSegment es
join sbssm.EventSegment es2
on es.Activity_ID = es2.Activity_ID and
es.Location_code <> es2.Location_code and
NOT (es.starttime >= es2.endtime or es.endtime <= es2.starttime)
group by es.Activity_ID, es.Location_code
) b on a.EventSegment_ID = b.EventSegment_ID
September 25, 2007 at 2:09 pm
Garsh, spoke too soon. Back to the drawing board...
September 26, 2007 at 8:05 am
;WITH Yak (ID1, ID2, RecID, RowID)
AS (
SELECT es1.EventSegment_ID,
es2.EventSegment_ID,
ROW_NUMBER() OVER (PARTITION BY es1.Location_Code, es1.Activity_ID ORDER BY es1.StartTime DESC) AS RecID,
COUNT(*) OVER (PARTITION BY es1.EventSegment_ID) AS RowID
FROM #EventSegment AS es1
LEFT JOIN #EventSegment AS es2 ON es2.Activity_ID = es1.Activity_ID
AND es2.Location_Code = es1.Location_Code
AND es2.StartTime < es1.EndTime
AND es2.StartTime <> es1.StartTime
AND es2.EndTime > es1.StartTime
AND es2.EndTime <> es1.EndTime
)
SELECT ID1
FROM Yak
WHERE RecID = 1
AND ID2 IS NULL
UNION
SELECT ID1
FROM Yak
WHERE RowID > 1
N 56°04'39.16"
E 12°55'05.25"
September 26, 2007 at 8:48 am
/*
almost! although that IS brilliant code. i can say that because i don't understand it 😉
I believe I have not defined the challenge clearly enough. let me retrace the steps:
*/
--the table
CREATE TABLE SBSSM.EventSegment
(
EventSegment_ID int IDENTITY (1, 1) NOT NULL,
StartTime datetime NOT NULL,
EndTime datetime NOT NULL,
Activity_ID int NOT NULL,
Location_code nvarchar(20) NOT NULL,
[DBTimeStamp] [timestamp] NULL,
CONSTRAINT IUC220 PRIMARY KEY(EventSegment_ID),
constraint ETGTST check (EndTime > StartTime)
)
GO
--the history data, with duplicates
insert into sbssm.eventsegment (StartTime, EndTime, Activity_ID, Location_code)
Select '2007-09-24 17:00','2007-09-24 18:00',1,'PL'
Union ALL Select '2007-09-24 16:00','2007-09-24 17:00',1,'PL'
Union ALL Select '2007-09-24 16:00','2007-09-24 17:00',1,'PL'
Union ALL Select '2007-09-24 17:00','2007-09-24 18:00',1,'PL'
Union ALL Select '2007-09-24 16:30','2007-09-24 17:30',1,'PL'
Union ALL Select '2007-09-24 15:00','2007-09-24 16:00',1,'PL'
Union ALL Select '2007-09-24 15:00','2007-09-24 16:00',1,'PL2'
Union ALL Select '2007-09-24 15:00','2007-09-24 16:00',2,'PL'
Union ALL Select '2007-09-24 15:00','2007-09-24 16:00',2,'PL3'
Union ALL Select '2007-09-24 18:00','2007-09-24 19:00',2,'PL'
GO
Select * from sbssm.EventSegment
go
-- row 10 is most recent record for location PL
-- row 9 is most recent record for location PL3
-- row 8 is most recent record for location PL that does not conflict with row 10
-- row 7 is most recent record for location PL2
-- row 5 is most recent record for location PL that does not conflict with row 10 as well
-- row 4 is eliminated because it is superceded by row 5 by reason of overlap for location PL
-- likewise for rows 3, 2, 1
--now:
WITH Yak (ID1, ID2, RecID, RowID)
AS (
SELECT es1.EventSegment_ID,
es2.EventSegment_ID,
ROW_NUMBER() OVER (PARTITION BY es1.Location_Code, es1.Activity_ID ORDER BY es1.StartTime DESC) AS RecID,
COUNT(*) OVER (PARTITION BY es1.EventSegment_ID) AS RowID
FROM sbssm.EventSegment AS es1
LEFT JOIN sbssm.EventSegment AS es2 ON es2.Activity_ID = es1.Activity_ID
AND es2.Location_Code = es1.Location_Code
AND es2.StartTime < es1.EndTime
AND es2.StartTime <> es1.StartTime
AND es2.EndTime > es1.StartTime
AND es2.EndTime <> es1.EndTime
)
SELECT ID1
FROM Yak
WHERE RecID = 1
AND ID2 IS NULL
UNION
SELECT ID1
FROM Yak
WHERE RowID > 1
--returns 5, 7, 9, 10
-- row 8 is missing.
/*
thanks for your response. i'll study your strategy and try to figure it out.
*/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply