SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Splitting rows at midnight
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tkizer
Almighty SQL Goddess

USA
36928 Posts

Posted - 11/11/2004 :  18:29:29  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Edited by - tkizer on 11/11/2004 18:41:38

AndyB13
Aged Yak Warrior

United Kingdom
583 Posts

Posted - 11/11/2004 :  20:42:39  Show Profile  Reply with Quote
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
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 11/11/2004 :  20:45:16  Show Profile  Visit Merkin's Homepage  Reply with Quote
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
Go to Top of Page

ehorn
Flowing Fount of Yak Knowledge

USA
1631 Posts

Posted - 11/11/2004 :  21:47:42  Show Profile  Reply with Quote
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 ! :)

Edited by - ehorn on 11/11/2004 22:23:10
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 11/12/2004 :  09:54:29  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 11/12/2004 :  10:36:31  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
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

Edited by - Seventhnight on 11/12/2004 10:39:03
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36928 Posts

Posted - 11/12/2004 :  12:44:48  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 11/12/2004 :  13:34:31  Show Profile  Reply with Quote
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-)
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36928 Posts

Posted - 11/12/2004 :  13:36:44  Show Profile  Visit tkizer's Homepage  Reply with Quote
No really. It's to pull up information about what an individual did during a day. The individuals are actually truck drivers.

Tara
Go to Top of Page

VIG
Yak Posting Veteran

Israel
86 Posts

Posted - 11/12/2004 :  13:41:58  Show Profile  Reply with Quote
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



Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36928 Posts

Posted - 11/12/2004 :  14:25:42  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

kselvia
Aged Yak Warrior

526 Posts

Posted - 11/12/2004 :  14:42:39  Show Profile  Reply with Quote
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.
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 11/12/2004 :  15:22:47  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36928 Posts

Posted - 11/12/2004 :  15:30:20  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 11/12/2004 :  15:32:03  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
no... your problem is that my numbers table starts at 0

Corey

Edited by - Seventhnight on 11/12/2004 15:33:17
Go to Top of Page

VIG
Yak Posting Veteran

Israel
86 Posts

Posted - 11/12/2004 :  15:34:35  Show Profile  Reply with Quote
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

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36928 Posts

Posted - 11/12/2004 :  15:39:20  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 11/12/2004 :  15:53:02  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
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
Go to Top of Page

VIG
Yak Posting Veteran

Israel
86 Posts

Posted - 11/12/2004 :  15:58:24  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36928 Posts

Posted - 11/12/2004 :  16:03:29  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Edited by - tkizer on 11/12/2004 16:04:06
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000