Splitting rows at midnight - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Splitting rows at midnight

Author

Topic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-11 : 18:29:29

I have this requirement to split an event (a row in the database) at midnight. There are three columns involved: datetime that the event occurred, duration of the event (in minutes), and code of the event type. For this problem, let's say that these events represent what a person does in the day. For simplicity's sake, let's ignore the meaning of the event type. So let's say that I went to bed at 11pm and slept for 8 hours, so I woke up at 7am (like that would ever happen on a non-snowboarding day

). What I need to display is 11pm for 60 minutes and 12am for 420 minutes.

I have it working except for one small problem. If after you split them there's still more than 1440 minutes, it doesn't work right. Check this out:


SET NOCOUNT ON

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

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

-- SELECT Activity, EventTime, EventDuration
-- FROM Event1

-- first SELECT gets events that don't span midnight
-- second gets the first part up to midnight of events that span midnight
-- third part gets me the second part past midnight of events that span midnight
SELECT Activity, EventTime, EventDuration
FROM Event1
WHERE EventTime <= DATEADD(mi, -EventDuration, DATEADD(d, 1, CONVERT(varchar(10), EventTime, 101)))
UNION ALL
SELECT Activity, EventTime, DATEDIFF(mi, EventTime, DATEADD(d, 1, CONVERT(varchar(10), EventTime, 101))) AS EventDuration
FROM 
	(
		SELECT Activity, EventTime, EventDuration
		FROM Event1
		WHERE EventTime > DATEADD(mi, -EventDuration, DATEADD(d, 1, CONVERT(varchar(10), EventTime, 101)))
	) t
UNION ALL
SELECT Activity, DATEADD(mi, DATEDIFF(mi, EventTime, DATEADD(d, 1, CONVERT(varchar(10), EventTime, 101))), EventTime) AS EventTime,
		EventDuration - DATEDIFF(mi, EventTime, DATEADD(d, 1, CONVERT(varchar(10), EventTime, 101))) AS EventDuration
FROM 
	(
		SELECT Activity, EventTime, EventDuration
		FROM Event1
		WHERE EventTime > DATEADD(mi, -EventDuration, DATEADD(d, 1, CONVERT(varchar(10), EventTime, 101)))
	) t
ORDER BY EventTime
             
DROP TABLE Event1

Result set:


Activity    EventTime                                              EventDuration 
----------- ------------------------------------------------------ ------------- 
4           2004-11-02 09:00:00.000                                240
3           2004-11-02 13:00:00.000                                570
4           2004-11-02 22:30:00.000                                90
4           2004-11-03 00:00:00.000                                380
1           2004-11-03 06:20:00.000                                800
3           2004-11-03 19:40:00.000                                260
3           2004-11-03 00:00:00.000                                1
2           2004-11-04 00:01:00.000                                1438
3           2004-11-04 23:59:00.000                                1
1           2004-11-05 00:00:00.000                                5
5           2004-11-05 00:05:00.000                                1435
5           2004-11-06 00:00:00.000                                2165
4           2004-11-07 12:05:00.000                                6

Notice the part in blue. All of the other splits worked fine, just not when you need to split it more than once. Also, more than two splits needed is possible.

The part in blue should become:


5           2004-11-06 00:00:00.000                                1440
5           2004-11-07 00:00:00.000                                725

Can anyone help me out with this? I just can't think of a set-based way to handle this.

Tara

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2004-11-11 : 20:42:39

I'll have a go

CASE WHEN EventDuration - DATEDIFF(mi, EventTime, DATEADD(d, 1, CONVERT(varchar(10), EventTime,101))) > 1440 THEN
EventDuration - DATEDIFF(mi, EventTime, DATEADD(d, +2, CONVERT(varchar(10), EventTime, 101)))
ELSE
EventDuration - DATEDIFF(mi, EventTime, DATEADD(d, 1, CONVERT(varchar(10), EventTime, 101)))
END AS EventDuration

This gave me the required results in QA

quote:

SET NOCOUNT ON

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

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

-- SELECT Activity, EventTime, EventDuration
-- FROM Event1

-- first SELECT gets events that don't span midnight
-- second gets the first part up to midnight of events that span midnight
-- third part gets me the second part past midnight of events that span midnight
SELECT Activity, EventTime, EventDuration
FROM Event1
WHERE EventTime <= DATEADD(mi, -EventDuration, DATEADD(d, 1, CONVERT(varchar(10), EventTime, 101)))
UNION ALL
SELECT Activity, EventTime, DATEDIFF(mi, EventTime, DATEADD(d, 1, CONVERT(varchar(10), EventTime, 101))) AS EventDuration
FROM
(
SELECT Activity, EventTime, EventDuration
FROM Event1
WHERE EventTime > DATEADD(mi, -EventDuration, DATEADD(d, 1, CONVERT(varchar(10), EventTime, 101)))
) t
UNION ALL
SELECT Activity, DATEADD(mi, DATEDIFF(mi, EventTime, DATEADD(d, 1, CONVERT(varchar(10), EventTime, 101))), EventTime) AS EventTime,
CASE WHEN EventDuration - DATEDIFF(mi, EventTime, DATEADD(d, 1, CONVERT(varchar(10), EventTime, 101))) > 1440 THEN
EventDuration - DATEDIFF(mi, EventTime, DATEADD(d, +2, CONVERT(varchar(10), EventTime, 101)))
ELSE EventDuration - DATEDIFF(mi, EventTime, DATEADD(d, 1, CONVERT(varchar(10), EventTime, 101)))
END AS EventDuration
FROM
(
SELECT Activity, EventTime, EventDuration
FROM Event1
WHERE EventTime > DATEADD(mi, -EventDuration, DATEADD(d, 1, CONVERT(varchar(10), EventTime, 101)))
) t
ORDER BY EventTime

DROP TABLE Event1

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

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-11-11 : 20:45:16

Damn, Andy beat me to it.

Another way is to have a date sequence table.. i.e.

Create Table DateSequence (SeqDate DateTime)
Declare @i int
SET @i = 0
WHILE @i < 31 BEGIN
INSERT INTO DateSequence VALUES ( DateAdd(d, @i, '1-Nov-2004') )
SEt @i = @i + 1
END

Then you can do this :

SELECT

Activity, EventTime, EventDuration, SeqDate,
CASE
WHEN DateDiff(d, EventTime, SeqDate) = 0 THEN EventTime
ELSE SeqDate END as TimeStartedToday,

CASE
WHEN DateAdd(n, EventDuration, EventTime) < DateAdd(d, 1, SeqDate) AND DateDiff(d, EventTime, SeqDate) = 0 THEN DateAdd(n, EventDuration, EventTime)
WHEN DateAdd(n, EventDuration, EventTime) < DateAdd(d, 1, SeqDate) THEN DateAdd(n, EventDuration, EventTime)
ELSE DateAdd(d, 1, SeqDate)
END as TimeFinishedToday,

--Repeating that..

DateDiff(n,
CASE
WHEN DateDiff(d, EventTime, SeqDate) = 0 THEN EventTime
ELSE SeqDate END,
CASE
WHEN DateAdd(n, EventDuration, EventTime) < DateAdd(d, 1, SeqDate) AND DateDiff(d, EventTime, SeqDate) = 0 THEN DateAdd(n, EventDuration, EventTime)
WHEN DateAdd(n, EventDuration, EventTime) < DateAdd(d, 1, SeqDate) THEN DateAdd(n, EventDuration, EventTime)
ELSE DateAdd(d, 1, SeqDate)
END ) as MinutesToday

FROM Event1

INNER JOIN DateSequence ON
DateDiff(d, EventTime, SeqDate) >= 0 AND
DateDiff(d, DateAdd(n, EventDuration, EventTime), DateSequence.SeqDate ) <= 0

WHERE
-- Stops 0 minute rows.. there may be a nicer way to do this
DateDiff(n,
CASE
WHEN DateDiff(d, EventTime, SeqDate) = 0 THEN EventTime
ELSE SeqDate END,
CASE
WHEN DateAdd(n, EventDuration, EventTime) < DateAdd(d, 1, SeqDate) AND DateDiff(d, EventTime, SeqDate) = 0 THEN DateAdd(n, EventDuration, EventTime)
WHEN DateAdd(n, EventDuration, EventTime) < DateAdd(d, 1, SeqDate) THEN DateAdd(n, EventDuration, EventTime)
ELSE DateAdd(d, 1, SeqDate)
END ) > 0

Damian

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-11-11 : 21:47:42

Nice challenge Tara. Thanks for posting it

select 
	activity,
	eventtime,
	case 
		when datediff(dd,eventtime,max_date) = 0 then datediff(mi,eventtime,max_date) 
		else datediff(mi,eventtime,convert(datetime,convert(varchar(12),dateadd(dd,1,eventtime))))  
	end EventDuration 
from
(
	select 
		activity,
		case 
			when dateadd(dd,n,eventtime) = eventtime then eventtime 
			else convert(datetime,convert(varchar(12),dateadd(dd,n,eventtime))) 
		end eventtime,
		max_date
	from
	(
		select activity,eventtime, eventduration, dateadd(mi,eventduration,eventtime) max_date
		from event1
	) d,numbers
	where convert(datetime,convert(varchar(12),dateadd(dd,n,eventtime))) < max_date
) d

and Damian, great idea with the date sequence table !! You killed me on performance...I used a tally table seeded at 0 but that filter is killing me (Nested Loop inner join cost of 38%)

So basically you da man ! :)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-12 : 09:54:29

Ehorn -- try changing your WHERE clause to:

where datediff(dd,eventtime,max_date) >= n

that should improve performance quite a bit. I always try to do this with tally tables as much as possible -- break the N out of all formulas and try to isolate it on one side of the operator. Really helps performance.

- Jeff

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-11-12 : 10:36:31

I've got to throw mine out there as usual


Create Table #numbers (n int identity(0,1), a int)
Insert into #numbers Select a = 1 from master.dbo.syscolumns

Declare @minDate datetime
Select @minDate = dateadd(dy,datediff(dy,0,min(eventTime)),0) From #event1

Select
	Activity,
	EventTime,
	EventDuration,
	EventDayStart,
	EventDayEnd,
	DayDuration = datediff(mi,EventDayStart,EventDayEnd)
From
	(
	Select 
		A.Activity, 
		EventTime,
		EventDuration,
		EventDayStart = case when dateadd(dy,datediff(dy,0,A.eventTime),0) = B.eventDate 
					then A.eventTime else EventDate end,
		EventDayEnd = case when dateadd(dy,datediff(dy,0,dateadd(mi,eventDuration,A.eventTime)),0) = B.EventDate 
					then dateadd(mi,eventDuration,A.eventTime) else B.EventDate+1 end
	From #event1 A
	Join (Select eventDate = @minDate+n from #numbers) B
	On (B.eventDate >= A.eventTime 
	and B.eventDate < dateadd(mi,eventDuration,A.eventTime))
	or (dateadd(dy,datediff(dy,0,A.eventTime),0) = B.eventDate)
	) Z


Drop Table #numbers

which gives:


4	2004-11-02 09:00:00.000	240	2004-11-02 09:00:00.000	2004-11-02 13:00:00.000	240
3	2004-11-02 13:00:00.000	570	2004-11-02 13:00:00.000	2004-11-02 22:30:00.000	570
4	2004-11-02 22:30:00.000	470	2004-11-02 22:30:00.000	2004-11-03 00:00:00.000	90
4	2004-11-02 22:30:00.000	470	2004-11-03 00:00:00.000	2004-11-03 06:20:00.000	380
1	2004-11-03 06:20:00.000	800	2004-11-03 06:20:00.000	2004-11-03 19:40:00.000	800
3	2004-11-03 19:40:00.000	261	2004-11-03 19:40:00.000	2004-11-04 00:00:00.000	260
3	2004-11-03 19:40:00.000	261	2004-11-04 00:00:00.000	2004-11-04 00:01:00.000	1
2	2004-11-04 00:01:00.000	1438	2004-11-04 00:01:00.000	2004-11-04 23:59:00.000	1438
3	2004-11-04 23:59:00.000	1	2004-11-04 23:59:00.000	2004-11-05 00:00:00.000	1
1	2004-11-05 00:00:00.000	5	2004-11-05 00:00:00.000	2004-11-05 00:05:00.000	5
5	2004-11-05 00:05:00.000	3600	2004-11-05 00:05:00.000	2004-11-06 00:00:00.000	1435
5	2004-11-05 00:05:00.000	3600	2004-11-06 00:00:00.000	2004-11-07 00:00:00.000	1440
5	2004-11-05 00:05:00.000	3600	2004-11-07 00:00:00.000	2004-11-07 12:05:00.000	725
4	2004-11-07 12:05:00.000	6	2004-11-07 12:05:00.000	2004-11-07 12:11:00.000	6

Corey

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-12 : 12:44:48

Thanks to everyone! I won't have a chance to review the solutions until possibly Monday though. But it looks like I've got several solutions to compare. Without spending too much time on it, does anyone have a feeling for which one performs the fastest? Not given the small sample data, but rather a larger set. I can't remember what the estimate is for the number of rows it'll have to churn through. The table will contain a few million rows, but this will only be run against one individual at a time. It's for a report.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-11-12 : 13:34:31

quote:
Originally posted by tduggan

but this will only be run against one individual at a time.

That has GOT to be the biggest lie in the IT business

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-12 : 13:36:44

No really. It's to pull up information about what an individual did during a day. The individuals are actually truck drivers.

Tara

VIG
Yak Posting Veteran

86 Posts

Posted - 2004-11-12 : 13:41:58

[code]declare @Event1 TABLE (Activity int NOT NULL, EventTime datetime NOT NULL, EventDuration int NOT NULL)

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

declare @tally table (n int)
insert @tally
SELECT 0 union
select 1 union
select 2 union
select 3 union
select 4 union
select 5 --etc

select
a.Activity
,a.EventStart EventTime
,datediff(mi,a.EventStart,a.EventEnd) EventDuration
FROM
(
SELECT Activity
,EventDuration
,case when EventTime >dateadd(d,datediff(d,0,EventTime),n)
then EventTime else dateadd(d,datediff(d,0,EventTime),n) end EventStart
,case when dateadd(mi,EventDuration,EventTime)<dateadd(d,datediff(d,0,EventTime),n+1)
then dateadd(mi,EventDuration,EventTime) else dateadd(d,datediff(d,0,EventTime),n+1)end EventEnd
,t.n
from @Event1 Event1
JOIN
@tally t
on t.n<=datediff(d,EventTime,dateadd(mi,EventDuration,EventTime))
) a
where datediff(mi,a.EventStart,a.EventEnd)>0
order by a.EventTime,a.Activity ,a.n

[/code]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-12 : 14:25:42

I was able to spend a little bit on this. A couple of the solutions don't work when you have to split more than 2 times. Jay's solution works, but when I try to incorporate Jeff's WHERE clause, I get inaccurate results. Any ideas?

Here's Jay's solution with the new sample data (notice the EventDuration of 10000, that requires several splits). I have Jeff's WHERE clause commented out in the solution. Uncomment it and comment out Jay's to see what I mean.



SET NOCOUNT ON

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

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

SELECT Activity, EventTime, EventDuration
FROM Event1
WHERE EventTime <= DATEADD(mi, -EventDuration, DATEADD(d, 1, CONVERT(varchar(10), EventTime, 101)))
UNION ALL
SELECT Activity, EventTime, DATEDIFF(mi, EventTime, DATEADD(d, 1, CONVERT(varchar(10), EventTime, 101))) AS EventDuration
FROM 
	(
		SELECT Activity, EventTime, EventDuration
		FROM Event1
		WHERE EventTime > DATEADD(mi, -EventDuration, DATEADD(d, 1, CONVERT(varchar(10), EventTime, 101)))
	) t
UNION ALL
select 
	activity,
	eventtime,
	case 
		when datediff(dd,eventtime,max_date) = 0 then datediff(mi,eventtime,max_date) 
		else datediff(mi,eventtime,convert(datetime,convert(varchar(12),dateadd(dd,1,eventtime))))  
	end EventDuration 
from
(
	select 
		activity,
		case 
			when dateadd(dd,n,eventtime) = eventtime then eventtime 
			else convert(datetime,convert(varchar(12),dateadd(dd,n,eventtime))) 
		end eventtime,
		max_date
	from
	(
		select activity,eventtime, eventduration, dateadd(mi,eventduration,eventtime) max_date
		from event1
	) d,numbers
	where convert(datetime,convert(varchar(12),dateadd(dd,n,eventtime))) < max_date
	--where datediff(dd,eventtime,max_date) >= n
) d
order by eventtime

DROP TABLE Event1

Expected result set:



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

Tara

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-11-12 : 14:42:39

Looks fun. Wish I wasn't so busy.

I think this might be a good job for CTE's in SQL 2005. If I have time I might investigate as an exercise since I have never used them.

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-11-12 : 15:22:47

Tara, mine worked fine with the multiple splits:


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

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


Create Table #numbers (n int identity(0,1), a int)
Insert into #numbers Select a = 1 from master.dbo.syscolumns

Declare @minDate datetime
Select @minDate = dateadd(dy,datediff(dy,0,min(eventTime)),0) From #event1

Select
	Activity,
--	EventTime,
	EventTime = EventDayStart,
--	EventDuration,
--	EventDayStart,
--	EventDayEnd,
	EventDuration = datediff(mi,EventDayStart,EventDayEnd)
From
	(
	Select 
		A.Activity, 
		EventTime,
		EventDuration,
		EventDayStart = case when dateadd(dy,datediff(dy,0,A.eventTime),0) = B.eventDate 
					then A.eventTime else EventDate end,
		EventDayEnd = case when dateadd(dy,datediff(dy,0,dateadd(mi,eventDuration,A.eventTime)),0) = B.EventDate 
					then dateadd(mi,eventDuration,A.eventTime) else B.EventDate+1 end
	From #event1 A
	Join (Select eventDate = @minDate+n from #numbers) B
	On (B.eventDate >= A.eventTime 
	and B.eventDate < dateadd(mi,eventDuration,A.eventTime))
	or (dateadd(dy,datediff(dy,0,A.eventTime),0) = B.eventDate)
	) Z


Drop Table #numbers
Drop Table #event1

the results:


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

Corey

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-12 : 15:30:20

Corey, when I run yours, the result set is missing the first 3 rows:



Activity    EventTime                                              EventDuration 
----------- ------------------------------------------------------ ------------- 
4           2004-11-02 09:00:00.000                                240
3           2004-11-02 13:00:00.000                                570
4           2004-11-02 22:30:00.000                                90

My Numbers table goes from 1-8000. I'm sure that's my problem.

Tara

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-11-12 : 15:32:03

no... your problem is that my numbers table starts at 0

Corey

VIG
Yak Posting Veteran

86 Posts

Posted - 2004-11-12 : 15:34:35

May be I do not understand something, but that wrong in my solution?

declare @Event1 TABLE  (Activity int NOT NULL, EventTime datetime NOT NULL, EventDuration int NOT NULL)

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

declare @tally table (n int)
insert @tally    
select number from master..spt_values where type='P'
/*now we can serve EventDuration<=256*24*60 =368640 min */
select 
      a.Activity 
      ,a.EventStart EventTime
      ,datediff(mi,a.EventStart,a.EventEnd) EventDuration
FROM
(
    SELECT Activity 
        ,EventDuration
        ,case when EventTime >dateadd(d,datediff(d,0,EventTime),n) 
              then EventTime else dateadd(d,datediff(d,0,EventTime),n) end EventStart	
	,case when dateadd(mi,EventDuration,EventTime)<dateadd(d,datediff(d,0,EventTime),n+1)
              then dateadd(mi,EventDuration,EventTime) else dateadd(d,datediff(d,0,EventTime),n+1)end EventEnd 
        ,t.n
from @Event1 Event1 
JOIN 
    @tally t
on t.n<=datediff(d,EventTime,dateadd(mi,EventDuration,EventTime))
) a
where datediff(mi,a.EventStart,a.EventEnd)>0
order by a.EventTime --,a.Activity  ,a.n

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

it's same results, that you expect

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-12 : 15:39:20

It works when you run the sample code that you provided. But when I run it against my Numbers table, it doesn't. I've always seens the tally table with 1-8000 in it, so that's what I loaded when I created it a while back.

Tara

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-11-12 : 15:53:02

I just started it with 0 becuase i wanted to include the day the event started... in case it started at midnight.

You could just union the zero in, or use a -1 in the query:


...
	Join (Select eventDate = @minDate+n-1 from #numbers) B
...

Corey

VIG
Yak Posting Veteran

86 Posts

Posted - 2004-11-12 : 15:58:24

Is that all problem you have ?

select 
      a.Activity 
      ,a.EventStart EventTime
      ,datediff(mi,a.EventStart,a.EventEnd) EventDuration
FROM
(
    SELECT Activity 
        ,EventDuration
        ,case when EventTime >dateadd(d,datediff(d,0,EventTime),n-1) 
              then EventTime else dateadd(d,datediff(d,0,EventTime),n-1) end EventStart	
	,case when dateadd(mi,EventDuration,EventTime)<dateadd(d,datediff(d,0,EventTime),n)
              then dateadd(mi,EventDuration,EventTime) else dateadd(d,datediff(d,0,EventTime),n)end EventEnd 
        ,t.n
from @Event1 Event1 
JOIN 
    @tally t
on t.n<=1+datediff(d,EventTime,dateadd(mi,EventDuration,EventTime))
) a
where datediff(mi,a.EventStart,a.EventEnd)>0
order by a.EventTime --,a.Activity  ,a.n

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-12 : 16:03:29

I have lots of problems, just only the one related to this "challenge"..

Thanks VIG and Corery for pointing out where the change needs to be for my Numbers table.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources