November 30, 2011 at 3:41 am
If i have the following table
CREATE TABLE [dbo].[Activities](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ResourceId] [int] NOT NULL,
[StartDate] [datetime] NOT NULL,
[EndDate] [datetime] NOT NULL,
CONSTRAINT [PK_Bookings] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
with the following data
insert into Activities (resourceid, startdate, enddate)
SELECT '31219','Nov 29 2011 8:00AM','Nov 29 2011 12:00PM' UNION ALL
SELECT '31219','Nov 29 2011 12:00PM','Nov 29 2011 6:00PM' UNION ALL
SELECT '31219','Nov 29 2011 6:00PM','Nov 30 2011 8:00AM' UNION ALL
SELECT '31219','Dec 3 2011 8:00AM','Dec 3 2011 12:00PM' UNION ALL
SELECT '31219','Dec 3 2011 10:00AM','Dec 3 2011 6:30PM' UNION ALL
SELECT '31219','Dec 4 2011 8:00AM','Dec 4 2011 12:00PM' UNION ALL
SELECT '31219','Dec 4 2011 12:00PM','Dec 4 2011 8:00PM' UNION ALL
SELECT '31219','Dec 4 2011 6:00PM','Dec 4 2011 11:00PM' UNION ALL
SELECT '31219','Dec 9 2011 6:00PM','Dec 10 2011 8:00AM' UNION ALL
SELECT '31219','Dec 10 2011 8:00AM','Dec 10 2011 12:00PM' UNION ALL
SELECT '31219','Dec 11 2011 9:00AM','Dec 11 2011 5:30PM'
Can anyone think of a way to merge activities that overlap or start at the end of a previous one, so that i only have one activity for a resource id?
Here are my expected results:-
delete from activities
insert into Activities (resourceid, startdate, enddate)
select 31219, '2011-11-29 08:00', '2011-11-30 08:00' union all
select 31219, '2011-12-03 08:00', '2011-12-03 18:30' union all
select 31219, '2011-12-04 08:00', '2011-12-04 23:00' union all
select 31219, '2011-12-09 18:00', '2011-12-10 12:00' union all
select 31219, '2011-12-11 09:00', '2011-12-11 17:30'
November 30, 2011 at 3:52 am
This sounds like a "gaps and islands" problem. Have a look at this - does it help?
John
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply