time plus duration new rows - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

time plus duration new rows

Author

Topic

lappin
Posting Yak Master

182 Posts

Posted - 2011-12-20 : 11:28:29

Hi, I have a table with a start-time and a duration. I have to create an additional (duplicate) row for each hour inside a view /function.
I have done this inside a function using a loop into a temp table. I wonder does anyone have a more efficient way of doing this as it is quite slow. Otherwise I may look at filling a table overnight or using an indexed view etc. Here is my function:
alter Function [dbo].[MyFunction](@Yr varchar(4))

RETURNS @Temp Table(fieldA varchar(25)
,fieldB char(10)
,fieldDateTime datetime
,Duration smallint

)
AS
begin

declare @i int=1
declare @j int=(Select MAX(duration)
from myTable
where [Year] =@Yr
)

while (@i<=@j)
begin
IF @i=1
begin
--One Hour
INSERT INTO @Temp( fieldA
,fieldB
,fieldDateTime
,[Duration]
)

Select fieldA
,fieldB
,fieldDateTime
,[Duration]
from myTable
where [Year] =@Yr

end
If @i>1
begin
--Multi-hours so add rows
INSERT INTO @Temp( fieldA
,fieldB
,fieldDateTime
,[Duration]
)
Select fieldA
,fieldB
,CASE when @i>1 then Dateadd(hour,@i-1,fieldDateTime) else fieldDateTime end as fieldDateTime
,[Duration]
from myTable
where ((duration)>=@i) and ((duration)>1)
and [Year] =@Yr
end
SET @i = @i + 1
End

RETURN
end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-20 : 11:52:17

[code]
;With CTE
AS(
Select fieldA
,fieldB
,fieldDateTime
,[Duration]
from myTable
where [Year] =@Yr
UNION ALL
SELECT fieldA
,fieldB
,DATEADD(hour,1,fieldDateTime)
,[Duration]
FROM CTE
WHERE DATEADD(hour,1,fieldDateTime)<=[Duration]
)

INSERT INTO @Temp( fieldA
,fieldB
,fieldDateTime
,[Duration]
)
SELECT *
FROM CTE

OPTION(MAXRECURSION 0)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lappin
Posting Yak Master

182 Posts

Posted - 2011-12-21 : 04:43:41

Thank improved the performance a huge amount. Thanks for your help.

lappin
Posting Yak Master

182 Posts

Posted - 2011-12-21 : 05:17:57

Sorry, I think I spoke too soon. Can you explain "WHERE DATEADD(hour,1,fieldDateTime)<=[Duration]". Duration holds the number of hours. So If I have a row:
FieldA, FieldB, fieldDateTime, Duration
500000, testXY, 2011-10-9 16:00, 3

It should be shown as:
FieldA, FieldB, fieldDateTime
500000, testXY, 2011-10-9 16:00
500000, testXY, 2011-10-9 17:00
500000, testXY, 2011-10-9 18:00

Using "WHERE DATEADD(hour,1,fieldDateTime)<=[Duration]" does not return additional rows.
I changed this line to:
WHERE [Duration]>1
but this again is very slow.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-21 : 12:05:54

[code]
;With CTE
AS(
Select fieldA
,fieldB
,fieldDateTime AS StartTime
,fieldDateTime
,[Duration]
from myTable
where [Year] =@Yr
UNION ALL
SELECT fieldA
,fieldB
,StartTime
,DATEADD(hour,1,fieldDateTime)
,[Duration]
FROM CTE
WHERE DATEDIFF(hour,StartTime,DATEADD(hour,1,fieldDateTime))<=[Duration]-1
)

INSERT INTO @Temp( fieldA
,fieldB
,fieldDateTime
,[Duration]
)
SELECT fieldA
,fieldB
,fieldDateTime
,[Duration]
FROM CTE

OPTION(MAXRECURSION 0)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources