CREATE TABLE Event(Activity int NOT NULL, EventTime datetime NOT NULL, EventDuration int NOT NULL)

INSERT INTO Event (Activity, EventTime, EventDuration)
SELECT 4, '08-02-2008 09:00', 240 UNION ALL
SELECT 3, '08-02-2008 13:00', 570 UNION ALL
SELECT 4, '08-02-2008 22:30', 470 UNION ALL
SELECT 1, '08-03-2008 06:20', 800 UNION ALL
SELECT 3, '08-03-2008 19:40', 261 UNION ALL
SELECT 2, '08-04-2008 00:01', 1438 UNION ALL
SELECT 3, '08-04-2008 23:59', 1 UNION ALL
SELECT 1, '08-05-2008 00:00', 5 UNION ALL
SELECT 5, '08-05-2008 00:05', 3600 UNION ALL
SELECT 4, '08-07-2008 12:05', 6 UNION ALL
SELECT 5, '08-07-2008 12:11', 10000 UNION ALL
SELECT 4, '08-14-2008 10:51', 3

Expected result set:

Activity    EventTime                                              EventDuration 
----------- ------------------------------------------------------ ------------- 
4           2008-08-02 09:00:00.000                                240
3           2008-08-02 13:00:00.000                                570
4           2008-08-02 22:30:00.000                                90
4           2008-08-03 00:00:00.000                                380
1           2008-08-03 06:20:00.000                                800
3           2008-08-03 19:40:00.000                                260
3           2008-08-04 00:00:00.000                                1
2           2008-08-04 00:01:00.000                                1438
3           2008-08-04 23:59:00.000                                1
1           2008-08-05 00:00:00.000                                5
5           2008-08-05 00:05:00.000                                1435
5           2008-08-06 00:00:00.000                                1440
5           2008-08-07 00:00:00.000                                725
4           2008-08-07 12:05:00.000                                6
5           2008-08-07 12:11:00.000                                709
5           2008-08-08 00:00:00.000                                1440
5           2008-08-09 00:00:00.000                                1440
5           2008-08-10 00:00:00.000                                1440
5           2008-08-11 00:00:00.000                                1440
5           2008-08-12 00:00:00.000                                1440
5           2008-08-13 00:00:00.000                                1440
5           2008-08-14 00:00:00.000                                651
4           2008-08-14 10:51:00.000                                3

select d.activity 
       ,case 
              when v.number = 0 then d.eventTime 
              else eventFinalEndDayStart 
              end EventTime
       ,case
              when d.SplitCount = 0 then eventDuration
              when v.number = 0 then datediff(minute, d.eventTime, EventTimeDayEnd)
              when v.number < d.SplitCount then 1440
              else datediff(minute, d.eventFinalEnddayStart, eventFinalEnd)
              end eventDuration
from   (
       select activity
              ,eventTime
              ,eventDuration
              ,dateadd(minute, eventduration, eventTime)       eventFinalEnd
              ,dateadd(day, 1, datediff(day, 0, eventTime))    EventTimeDayEnd
              ,dateadd(day, 0, datediff(day, 0, dateadd(minute, eventduration, eventTime))) eventFinalEndDayStart
              ,datediff(day, eventTime, dateadd(minute, eventduration, eventTime)) SplitCount
       from   event e
       ) d
join   master..spt_values v
       on  v.type = 'p'
       and v.number <= d.SplitCount
       and case
              when d.SplitCount = 0 then eventDuration
              when v.number = 0 then datediff(minute, d.eventTime, EventTimeDayEnd)
              when v.number < d.SplitCount then 1440
              else datediff(minute, d.eventFinalEnddayStart, eventFinalEnd)
              end > 0

--I had one extra line of output: (is this missing from your expected results?)
--4           2008-08-14 10:51:00.000    3

SELECT		CASE
			WHEN Number = 0 THEN s.EventTime
			ELSE DATEADD(DAY, DATEDIFF(DAY, 0, DATEADD(DAY, Number, s.EventTime)), 0)
		END AS EventTime,
		CASE
			WHEN Number = 0 AND Span = 0 THEN DATEDIFF(MINUTE, s.EventTime, s.EndTime)
			WHEN Number = 0 AND Span > 0 THEN 1440 - DATEDIFF(MINUTE, 0, s.EventTime) % 1440
			WHEN Number = Span AND Span > 0 THEN DATEDIFF(MINUTE, 0, s.EndTime) % 1440
			ELSE 1440
		END AS Duration
FROM		(
			SELECT	EventTime,
				DATEADD(MINUTE, EventDuration, EventTime) AS EndTime,
				DATEDIFF(DAY, EventTime, DATEADD(MINUTE, EventDuration - 1, EventTime)) AS Span
			FROM	@Sample
		) AS s
INNER JOIN	@Tally AS t ON t.Number <= s.Span

quote:

---

Originally posted by Peso

Tara, the bad data value of 10833601 is 7525 different days (20+ years), which means you tally table must exceed that number which you do if your numbers in tally table are 0 to 7999.

---

WHERE
	DATEDIFF(mi, EventStart, EventEnd) > 0 AND 
	EventStart >= @StartDate AND 
	EventStart < @EndDate + 1

2008-08-03 00:00:00.000 380
2008-08-03 06:20:00.000 800
2008-08-03 19:40:00.000 260
2008-08-04 00:00:00.000 1
2008-08-04 00:01:00.000 1438
2008-08-04 23:59:00.000 1
2008-08-05 00:00:00.000 5
2008-08-05 00:05:00.000 1435

DECLARE	@Event TABLE
	(
		Activity INT,
		EventTime DATETIME,
		EventDuration INT,
		PRIMARY KEY CLUSTERED
		(
			Activity,
			EventTime,
			EventDuration
		)
	)

SET DATEFORMAT MDY

INSERT	@Event
SELECT	4, '08-02-2008 09:00',   240 UNION ALL
SELECT	3, '08-02-2008 13:00',   570 UNION ALL
SELECT	4, '08-02-2008 22:30',   470 UNION ALL
SELECT	1, '08-03-2008 06:20',   800 UNION ALL
SELECT	3, '08-03-2008 19:40',   261 UNION ALL
SELECT	2, '08-04-2008 00:01',  1438 UNION ALL
SELECT	3, '08-04-2008 23:59',     1 UNION ALL
SELECT	1, '08-05-2008 00:00',     5 UNION ALL
SELECT	5, '08-05-2008 00:05',  3600 UNION ALL
SELECT	4, '08-07-2008 12:05',     6 UNION ALL
SELECT	5, '08-07-2008 12:11', 10000 UNION ALL
SELECT	4, '08-14-2008 10:51',     3

DECLARE	@Tally TABLE
	(
		Number INT PRIMARY KEY CLUSTERED
	)

INSERT	@Tally
SELECT	0 UNION ALL
SELECT	1 UNION ALL
SELECT	2 UNION ALL
SELECT	3 UNION ALL
SELECT	4 UNION ALL
SELECT	5 UNION ALL
SELECT	6 UNION ALL
SELECT	7 UNION ALL
SELECT	8 UNION ALL
SELECT	9

DECLARE	@StartDate DATETIME,
	@EndDate DATETIME

SELECT	@StartDate = '08-03-2008',
	@EndDate = '08-05-2008'

SELECT	d.Activity,
	d.EventTime,
	d.EventDuration
FROM	(
		SELECT		s.Activity,
				CASE
					WHEN t.Number = 0 THEN s.EventTime
					ELSE DATEADD(DAY, DATEDIFF(DAY, 0, DATEADD(DAY, t.Number, s.EventTime)), 0)
				END AS EventTime,
				CASE
					WHEN t.Number = 0 AND s.Span = 0 THEN s.EventDuration
					WHEN t.Number = 0 AND s.Span > 0 THEN 1440 - DATEDIFF(MINUTE, 0, s.EventTime) % 1440
					WHEN t.Number = s.Span AND s.Span > 0 THEN DATEDIFF(MINUTE, 0, s.EndTime) % 1440
					ELSE 1440
				END AS EventDuration
		FROM		(
					SELECT	Activity,
						EventTime,
						EventDuration,
						DATEADD(MINUTE, EventDuration, EventTime) AS EndTime,
						DATEDIFF(DAY, EventTime, DATEADD(MINUTE, EventDuration - 1, EventTime)) AS Span
					FROM	@Event
					WHERE	DATEADD(MINUTE, EventDuration, EventTime) >= @StartDate		-- Fetch only those original records that will start after (or span) @StartDate limit
						AND EventTime < DATEADD(DAY, 1, @EndDate)			-- Fetch only those original records that will start before @EndDate limit
				) AS s
		INNER JOIN	@Tally AS t ON t.Number <= s.Span
	) AS d
WHERE	d.EventTime >= @StartDate				-- Fetch only those eventually splitted records that will start after @StartDate limit
	AND d.EventTime < DATEADD(DAY, 1, @EndDate)		-- Fetch only those eventually splitted records that will start before @EndDate limit

SELECT	d.Activity,
	d.EventTime,
	d.EventDuration
FROM	(
		SELECT		s.Activity,
				CASE
					WHEN t.Number = 0 THEN s.EventTime								-- First day of span (or "same day event") always original date and time
					ELSE DATEADD(DAY, DATEDIFF(DAY, '19000101', DATEADD(DAY, t.Number, s.EventTime)), '19000101')	-- All other days in span, always increase date and use 00:00 as time
				END AS EventTime,
				CASE
					WHEN s.Span = 0 THEN s.EventDuration								-- "Same day" event
					WHEN t.Number = 0 THEN 1440 - DATEDIFF(MINUTE, '19000101', s.EventTime) % 1440			-- First day of span
					WHEN t.Number = s.Span THEN DATEDIFF(MINUTE, '19000101', s.EndTime) % 1440			-- Last day of span
					ELSE 1440											-- All other days in between span
				END AS EventDuration
		FROM		(
					SELECT	Activity,
						EventTime,
						EventDuration,
						DATEADD(MINUTE, EventDuration, EventTime) AS EndTime,					-- Calculate ending time
						DATEDIFF(DAY, EventTime, DATEADD(MINUTE, EventDuration - 1, EventTime)) AS Span		-- Calculate number of span
					FROM	@Event
					WHERE	DATEADD(MINUTE, EventDuration, EventTime) >= @StartDate					-- Fetch only those original records that will start after, or span, @StartDate limit
						AND EventTime < DATEADD(DAY, 1, @EndDate)						-- Fetch only those original records that will start before @EndDate limit
				) AS s
		INNER JOIN	@Tally AS t ON t.Number <= s.Span
	) AS d
WHERE	d.EventTime >= @StartDate													-- Fetch only those (splitted records) that will start after @StartDate limit
	AND d.EventTime < DATEADD(DAY, 1, @EndDate)											-- Fetch only those (splitted records) that will start before @EndDate limit

  |--Compute Scalar
       |--Nested Loops(Inner Join)
            |--Compute Scalar
            |    |--Clustered Index Scan(@Event)
            |--Clustered Index Seek(@Tally)

INNER LOOP JOIN	@Tally AS t ON t.Number <= s.Span

  |--Compute Scalar
       |--Nested Loops(Inner Join)
            |--Clustered Index Scan(@Event)
            |--Clustered Index Seek(@Tally)