How to split a single record in multiple records based on a datetime period?
-
Rehman Rafique (1/6/2011)
Hey jeff, thnx for ur critical view on my code. it runs fine, i have tested it but it works for on equipment....just wanted to try another way to solve it....thou it can b modified accordinglyanyway thnx mayn.....Cheers
Heh... While loops don't usually "run fine", Rehman. They normally just run slow. Take a look at the following article to see what I mean... mayn... 😉
http://www.sqlservercentral.com/articles/T-SQL/62867/
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
ok ill check it out.....
with "runs fine" i meant dat it provides da required result, i wasnt pointing towards it's execution time etc...
-
Rehman Rafique (1/6/2011)
ok ill check it out.....with "runs fine" i meant dat it provides da required result, i wasnt pointing towards it's execution time etc...
Understood and appreciated. Since you're a bit new to this forum, I just thought I'd let you know that most WHILE loop solutions are pretty much jumped on because of their general performance problems.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
thnx jeff..... much appreciated!!!
-
Thanks very much who has contributed to this thread. I really appreciate it.
Cheers,
...
XML DB Java Developer
'enthusiastic about software solutions for real life' -
Using an in-line Table Function to split the time.
Go
If object_id('SplitTimeCrossingMidnight') is not null Drop Function SplitTimeCrossingMidnight
Go
Create Function SplitTimeCrossingMidnight(
@StartDateTime SmalldateTime,
@EndDateTime SmalldateTime
)
Returns Table
As
Return(
With DT as
(select
StartdateTime=@StartdateTime,
EndDateTime=Case when datediff(dd,@StartDateTime,@EndDateTime)=0
Then @EndDateTime
Else cast(dateadd(dd,datediff(dd,0,@StartDateTime)+1,0) as SmalldateTime) -- Midnight
End
Union all
Select
StartdateTime=EndDateTime,
EndDateTime=Case when datediff(dd,EndDateTime,@EndDateTime)=0
Then @EndDateTime
Else cast(dateadd(dd,datediff(dd,0,EndDateTime)+1,0) as SmalldateTime) -- Midnight
End
From DT
Where
EndDateTime<@EnddateTime
)
Select * from DT
)
Go
If Object_id('Tempdb..#MyTable') is Not Null Drop Table #MyTable
CREATE TABLE #MyTable (
equipment varchar(20) NOT NULL,
date_start datetime NOT NULL,
date_end datetime NOT NULL
)
INSERT INTO #MyTable (equipment, date_start, date_end)
SELECT 'equip1', '2009-11-29T06:00:00', '2009-12-01T18:30:00' UNION ALL
SELECT 'equip2', '2008-12-30T11:00:00', '2009-01-02T18:30:25' UNION ALL
SELECT 'equip3', '2008-05-21T11:15:00', '2008-05-21T22:45:00'
Select
equipment,
date_start =StartdateTime,
hours_per_day =DATEDIFF(mi,DT.StartdateTime,DT.EndDateTime)/60.0
From #MyTable MT
Cross Apply(
Select
StartdateTime,
EndDateTime
From dbo.SplitTimeCrossingMidnight(MT.date_start,MT.date_end)
) DT
-
Although you can certainly do some clever things with recursive CTE's, I generally try to avoid them because they're so very expensive for CPU and other resources compared to things like Tally Table solutions and, sometimes, even While Loops. And, no... I don't expect anyone to take my word for it... here's a test using code from this thread...
--===== Build and populate the test table with some additional test data-- to make the test last long enough to measure the differences
If Object_id('Tempdb..#MyTable') is Not Null Drop Table #MyTable
CREATE TABLE #MyTable (
equipment varchar(20) NOT NULL,
date_start datetime NOT NULL,
date_end datetime NOT NULL
)
;
INSERT INTO #MyTable (equipment, date_start, date_end)
SELECT 'equip1', '2009-11-28 00:00:00', '2009-11-29 05:15:00' UNION ALL
SELECT 'equip1', '2009-11-29 06:00:00', '2009-12-01 18:30:00' UNION ALL
SELECT 'equip2', '2008-12-30 11:00:00', '2009-01-02 18:30:25' UNION ALL
SELECT 'equip3', '2008-05-21 11:15:00', '2008-05-21 22:45:00' UNION ALL
SELECT 'equip4', '2000-01-01 00:00:00', '2010-01-01 00:00:00' UNION ALL
SELECT 'equip5', '2000-01-01 00:00:00', '2010-01-01 00:00:00' UNION ALL
SELECT 'equip6', '2000-01-01 00:00:00', '2010-01-01 00:00:00'
;
GO
--========================================================================
;
GO
--===== Andrew''s Tally Table Solution
;WITH ctePrep AS (
SELECT equipment, date_start, date_end,
DATEADD(day, DATEDIFF(day, 0, date_start), 0) AS date_base,
DATEDIFF(day, date_start, date_end) AS day_num
FROM #MyTable
)
SELECT
equipment,
CASE WHEN (T.N = 0) THEN
D.date_start
ELSE
DATEADD(day, T.N, date_base)
END AS date_start,
CAST (
CASE WHEN (D.day_num = 0) THEN
D.date_end - D.date_start
WHEN (T.N = 0) THEN
DATEADD(day, 1, D.date_base) - D.date_start
WHEN (T.N = D.day_num) THEN
D.date_end - DATEADD(day, D.day_num, D.date_base)
ELSE 1 END AS float
) * 24.0 AS hours_per_day
FROM ctePrep D
INNER JOIN dbo.Tally T ON (T.N BETWEEN 0 AND D.day_num)
ORDER BY equipment, date_start
;
GO 5
--========================================================================
;
GO
--===== Recursive CTE function solution
Select
equipment,
date_start =StartdateTime,
hours_per_day =DATEDIFF(mi,DT.StartdateTime,DT.EnddateTime)/60.0
From #MyTable MT
Cross Apply(
Select
StartdateTime,
EnddateTime
From dbo.SplitTimeCrossingMidnight(MT.date_start,MT.date_end)
) DT
OPTION(MAXRECURSION 0)
;
GO 5
Here're the results from SQL Profiler. Check out the CPU column.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks Jeff for following this up.
--Andrew
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply