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 2005 Forums
 Transact-SQL (2005)
 Splitting rows at midnight
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 6

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 09/08/2008 :  14:53:12  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Edited by - tkizer on 09/08/2008 17:54:13

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 09/08/2008 :  15:21:38  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

USA
35940 Posts

Posted - 09/08/2008 :  15:48:40  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
5932 Posts

Posted - 09/08/2008 :  17:41:28  Show Profile  Reply with Quote
>>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

Sweden
29910 Posts

Posted - 09/08/2008 :  17:45:09  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"

Edited by - SwePeso on 09/09/2008 04:21:45
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 09/08/2008 :  17:52:43  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Edited by - tkizer on 09/08/2008 17:54:36
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 09/08/2008 :  18:02:43  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"

Edited by - SwePeso on 09/08/2008 18:24:35
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 09/08/2008 :  18:07:19  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"

Edited by - SwePeso on 09/08/2008 18:08:15
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 09/08/2008 :  18:20:00  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
35940 Posts

Posted - 09/08/2008 :  18:25:05  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Sweden
29910 Posts

Posted - 09/08/2008 :  18:25:43  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

USA
35940 Posts

Posted - 09/08/2008 :  18:30:03  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Sweden
29910 Posts

Posted - 09/08/2008 :  18:46:48  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

USA
35940 Posts

Posted - 09/08/2008 :  19:20:48  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Edited by - tkizer on 09/08/2008 19:21:28
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 09/09/2008 :  04:12:37  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Sweden
29910 Posts

Posted - 09/09/2008 :  05:00:18  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Sweden
29910 Posts

Posted - 09/09/2008 :  05:11:32  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"

Edited by - SwePeso on 09/09/2008 05:25:03
Go to Top of Page

David0129
Starting Member

USA
8 Posts

Posted - 09/09/2008 :  08:26:16  Show Profile  Visit David0129's Homepage  Reply with Quote
** 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

Sweden
29910 Posts

Posted - 09/09/2008 :  09:04:46  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

USA
8 Posts

Posted - 09/09/2008 :  09:20:31  Show Profile  Visit David0129's Homepage  Reply with Quote
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

USA
491 Posts

Posted - 09/09/2008 :  10:43:16  Show Profile  Reply with Quote
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
Page: of 6 Previous Topic Topic Next Topic  
Next Page
 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.27 seconds. Powered By: Snitz Forums 2000