spread duration out over 25 minute intervals

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)

spread duration out over 25 minute intervals

Author

Topic

bushfoot
Yak Posting Veteran

53 Posts

Posted - 2011-01-25 : 11:31:11

I have a table that contains the start time as second of day and a duration.

I need to spread the duration over the number of seconds the duration covers.

For example, if it starts at 36 ( the 36 second of the day) and the duration is 5 seconds, then I need to peg 1 second of duration to 36,37,38,39,40.

Any suggestion on how I can do this?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-01-25 : 11:43:36

Something like this?

declare @table table(sec int,duration int)

INSERT INTO @table
SELECT 1,10 UNION ALL
SELECT 10,8 UNION ALL
SELECT 20,12 UNION ALL
SELECT 25,3 UNION ALL
SELECT 30,1

SELECT sec,duration,sec+number
from
@table t
CROSS APPLY
(select number
from master..spt_values
where type = 'P' and number < duration
) a

Jim

Everyday I learn something that somebody else already knew

bushfoot
Yak Posting Veteran

53 Posts

Posted - 2011-01-25 : 11:50:58

Jim:
This is very slick and looks like it will work. I will go look into Cross Apply and make sure I understand how it works. I actually have to find a way to implement this in a cube. I thought if I could figure it out in sql , it will be easier to implement in SSAS.
Thanks again!
Kel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources