| Author |
 Topic  |
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
 Posted - 11/11/2004 : 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 |
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
|
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!
Australia
4970 Posts |
Posted - 11/11/2004 : 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
Flowing Fount of Yak Knowledge
USA
1629 Posts |
Posted - 11/11/2004 : 21:47:42
|
Nice challenge Tara. Thanks for posting itselect
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 |
|
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 11/12/2004 : 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
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 11/12/2004 : 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
 |
Edited by - Seventhnight on 11/12/2004 10:39:03 |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 11/12/2004 : 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 - 11/12/2004 : 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
USA
35007 Posts |
Posted - 11/12/2004 : 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
Israel
86 Posts |
Posted - 11/12/2004 : 13:41:58
|
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
|
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 11/12/2004 : 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 - 11/12/2004 : 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
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 11/12/2004 : 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
USA
35007 Posts |
Posted - 11/12/2004 : 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
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 11/12/2004 : 15:32:03
|
no... your problem is that my numbers table starts at 0
Corey
|
Edited by - Seventhnight on 11/12/2004 15:33:17 |
|
|
|
VIG
Yak Posting Veteran
Israel
86 Posts |
Posted - 11/12/2004 : 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
USA
35007 Posts |
Posted - 11/12/2004 : 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
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 11/12/2004 : 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
Israel
86 Posts |
Posted - 11/12/2004 : 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
USA
35007 Posts |
Posted - 11/12/2004 : 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 |
Edited by - tkizer on 11/12/2004 16:04:06 |
|
|
| |
Topic |
|
|
|
Splitting rows at midnight
