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 2005 Forums
 Transact-SQL (2005)
 Splitting rows at midnight

Author  Topic 

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-08 : 14:53:12
About 4 years ago, I posted a question here in regards to splitting rows at midnight. Many solutions were provided, but I ended up going with Corey's.

At the time, we were on SQL Server 2000. We've been on SQL Server 2005 for about 2 years. Performance is getting terrible on this report especially for our larger customers, names you'd recognize but not sure if I can mention. I'm wondering if there is a more efficient solution in SQL Server 2005.

Here is the original thread: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=42516

And here's the details, sample data, and expected result set:

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. These events represent what a truck driver does in the day. For simplicity's sake, let's ignore the meaning of the event type. So let's say that a truck driver was driving for 3 hours starting at 10pm. What I need to display is 10pm for 120 minutes and 12am for 60 minutes. I may need several splits as EventDuration may span several days or months. For instance, when a trucker driver is on vacation, the event is recorded as off-duty. Our system contains continuous data for each truck driver.


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


Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-08 : 15:21:38
Hi Tara.

I suggest you use CROSS APPLY for this to split up day(s) if spanning multiple days.
Within the CROSS APPLY you can use your 8000 record Number table because that equals more than 20 years of data.
I find it hard that a driver can work 20+ years straight.
When query returns data from CROSS APPLY, use some of the previously suggested methods to calculate
1) the remaining minutes until midnight for starting date
2) 1440 minutes per day for every day between (not including) start date and "end date)
3) the number of minutes from midnight to "end date" for last day.

If some one doesn't beat me to it, I will post a working solution for you when I come back to work tomorrow, ok?

PS. Do you imply that EXPERTS ONLY should answer?



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-08 : 15:48:40
When you say 20 years of data, are you referring to the possibility of 1,440 events in one day since we track each minute?

I checked the database and the highest number of "duty status"/event changes in one day is 105.

I look forward to your solution tomorrow as I don't have a clue where to put CROSS APPLY in there.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-08 : 17:41:28
>>are you referring to the possibility of 1,440 events in one day since we track each minute?
My guess is he was referring to the possibility of a EventDuration of 10 million minutes.

This solution if very similar to Corey's (still 2000) just organized slightly differently to do most of the work before joining to a Numbers table. So maybe not any better...

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



Be One with the Optimizer
TG
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-08 : 17:45:09
Try this for fun!
By the way, I was a little confused when the 3 minute sample data for Aug 14 was missing in the expected resultset.

< See suggestion further down >


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-08 : 17:52:43
We do have a value of 10833601 in the EventDuration column, which must be bad data since this app has only been live for about 5 years and the EventTime shows 1988. We need to support this bad data though.

Peso,

Is it possible to modify the code so that the splits show midnight rather than the original date and time? For instance, in my result set, we've got '2008-08-08 00:00:00.000', but in yours it shows '2008-08-07 12:11:00.000' for the same row.

And sorry about the missing row in my result set. I've edited the original post.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-08 : 18:02:43
Thank you Tara for the excellent feedback.
Here is an efficient solution both for SQL Server 2000 and SQL Server 2005
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


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-08 : 18:07:19
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.


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-08 : 18:20:00
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.




If that's the case, then no one must've run a report for this truck driver so the issue hasn't been noticed.

I will be working on integrating your solution with our code very soon. The stored procedure is 233 lines of code, so I've got figure out where to modify it.

Luckily, we just purchased new hardware for the performance environment, so I'll be able to test out the old solution (Corey's), TG's solution, and your solution on beefy new hardware. Plus I'll be the only one on the system during the tests as development is currently working on rewriting code so they don't have time for performance tests. 8 CPUs and 16GB of RAM are all mine!

The table has about 18 million rows in it in our performance environment and 21 million rows in production. We retain only 6 months of data. The data size will get bigger though as we get more companies. We are going to need to partition the table in the very near future.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-08 : 18:25:05
Looking at the old solution, I'm using VIG's solution not Corey's.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-08 : 18:25:43
I posted an edited version of my suggestion just a minut ago.
Please make sure you use the latest version.

Thank you.



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-08 : 18:30:03
Thank you, Peso. I've grabbed the new version. I'll get back to you tomorrow with performance results. I'll spend the rest of my day today modifying the code to include your solution.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-08 : 18:46:48
Based on my recent findings, there should be a small (maybe negligable) difference. VIG has an extra COMPUTE SCALAR.
Of what I can see, the WHERE clause in VIG's suggestion is only there to prevent "false records", which is records having EventDuration added to EventTime and that time ends exactly on midnight.
But the WHERE clause is calculated for every record in the JOIN not only false ones.

In my suggestion I don't join false records and that might give my suggestion a slight advantage.



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-08 : 19:20:48
I can't figure out what to put in the WHERE clause. Here's what we have in the original solution:

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


In Peso's solution, we don't have the EventStart column and I can't figure out how to duplicate the functionality. The code is used in a report that accepts input parameters of @StartDate and @EndDate. Both will be a date with a time at midnight. We need all events for the entire range of days. So let's says the input parameters are 08-03-2008 to 08-05-2008, I need the following output:


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


How do I filter the data to get that?

Here's the input stuff:

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

Note that I can't just wrap it into a derived table and grab only the data that I need. I need to filter as I'm going for performance reasons. A truck driver could have 6 months of data (our data retention policy) and the user running the report might only want to see a couple of days of data. We need to process only those couple of days plus the event before and the event after to get continuous events.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-09 : 04:12:37
Ah.. New requirement with a partial resultset due to @StartDate and @EndDate parameters
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



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-09 : 05:00:18
Some estethical changes and more comments...
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



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-09 : 05:11:32
For my suggestion posted 09/09/2008 : 05:00:18 I can see the execution plan is

|--Compute Scalar
|--Nested Loops(Inner Join)
|--Compute Scalar
| |--Clustered Index Scan(@Event)
|--Clustered Index Seek(@Tally)
If you query hint the JOIN to
INNER LOOP JOIN	@Tally AS t ON t.Number <= s.Span
you get rid of one Compute Scalar operation.

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


This, however, may not work in your environment. I have only a limited set of sample data.


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

David0129
Starting Member

8 Posts

Posted - 2008-09-09 : 08:26:16
** First time poster, long time browser **

I am a .NET programmer so most of my work is in the front-end, but I couldn't resist the challenge. Since nobody has posted this type of solution, is there something fundamentally wrong with this approach?

David

DECLARE @Event TABLE (Activity int NOT NULL, EventTime datetime NOT NULL, EventDuration int PRIMARY KEY CLUSTERED)

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

--Create a table of dates
DECLARE @dates TABLE(date DATETIME PRIMARY KEY CLUSTERED)
DECLARE @start DATETIME
DECLARE @END DATETIME
DECLARE @x INT
DECLARE @totalDays INT
SELECT @start = (SELECT TOP 1 DATEDIFF(dd,0,eventTime) FROM @event ORDER BY EventTime)
SELECT @end = (SELECT TOP 1 DATEADD(mi, eventDuration, EventTime) FROM @Event ORDER BY DATEADD(mi, eventDuration, EventTime) DESC)

SELECT @totalDays = DATEDIFF(dd, @start, @end)
SELECT @x = 0
WHILE @x <= @totalDays
BEGIN
INSERT INTO @dates SELECT @start
SELECT @start = DATEADD(dd, 1, @start)
SELECT @x = @x + 1
END


SELECT
--date,
activity,
CASE DATEDIFF(dd,0,eventTime) WHEN date THEN eventTime ELSE date END as date,
DATEDIFF(
mi,
CASE DATEDIFF(dd,0,eventTime) WHEN date THEN eventTime ELSE date END,
CASE DATEDIFF(dd,0,DATEADD(mi, eventDuration, eventTime))
WHEN date THEN DATEADD(mi, eventDuration, eventTime)
ELSE DATEADD(dd,1,date) END
) as minutes

FROM
@Event e INNER JOIN @dates d
ON

(DATEADD(dd,0, DATEDIFF(dd,0,eventTime)) <= date AND DATEADD(mi,eventDuration,eventTime) > date)
ORDER BY d.date

Dave
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-09 : 09:04:46
I can see a couple of problems with this approach.
Speed is one of them.

Also, how do you include the @StartDate and @EndDate filtering?
For record {4, '08-02-2008 22:30', 470} should be split into one 90 minute part and one 380 minute part, and you want the 380 minute part (as of Tara's example).



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

David0129
Starting Member

8 Posts

Posted - 2008-09-09 : 09:20:31
Peso,

The result set can be filtered by setting the @start and @end variables instead of setting them to the values in the Event table. Based on Tara's example of startDate and endDate of '08-03-2008' and '08-05-2008' you'll get the results she posted:

DECLARE @Event TABLE (Activity int NOT NULL, EventTime datetime NOT NULL, EventDuration int PRIMARY KEY CLUSTERED)

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

--Create a table of dates
DECLARE @dates TABLE(date DATETIME PRIMARY KEY CLUSTERED)
DECLARE @start DATETIME
DECLARE @END DATETIME
DECLARE @x INT
DECLARE @totalDays INT
SELECT @start = '08-03-2008' --(SELECT TOP 1 DATEDIFF(dd,0,eventTime) FROM @event ORDER BY EventTime)
SELECT @end = '08-05-2008'--(SELECT TOP 1 DATEADD(mi, eventDuration, EventTime) FROM @Event ORDER BY DATEADD(mi, eventDuration, EventTime) DESC)

SELECT @totalDays = DATEDIFF(dd, @start, @end)
SELECT @x = 0
WHILE @x <= @totalDays
BEGIN
INSERT INTO @dates SELECT @start
SELECT @start = DATEADD(dd, 1, @start)
SELECT @x = @x + 1
END


SELECT
--date,
activity,
CASE DATEDIFF(dd,0,eventTime) WHEN date THEN eventTime ELSE date END as date,
DATEDIFF(
mi,
CASE DATEDIFF(dd,0,eventTime) WHEN date THEN eventTime ELSE date END,
CASE DATEDIFF(dd,0,DATEADD(mi, eventDuration, eventTime))
WHEN date THEN DATEADD(mi, eventDuration, eventTime)
ELSE DATEADD(dd,1,date) END
) as minutes

FROM
@Event e INNER LOOP JOIN @dates d
ON

(DATEADD(dd,0, DATEDIFF(dd,0,eventTime)) <= date AND DATEADD(mi,eventDuration,eventTime) > date)
ORDER BY CASE DATEDIFF(dd,0,eventTime) WHEN date THEN eventTime ELSE date END

Dave
Go to Top of Page

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-09-09 : 10:43:16
Would a recursive CTE approach fill this requirement? I'm working on one but I'm ironing out an issue with dates that span 2 days or more.
Go to Top of Page
    Next Page

- Advertisement -