Adding half hour intervals to time

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

General SQL Server Forums

New to SQL Server Programming

Adding half hour intervals to time

Author

Topic

ertweety
Starting Member

15 Posts

Posted - 2014-10-06 : 11:29:43

I have data that looks like the following.

ID Date Time Length Interval_Num
1 10/11/2014 9:00 420 14

Basically, length represents the # of minutes a person is scheduled for. We have 30 minute intervals. Interval_Num = (Length/30).
I need the data to show each interval. For the above example it should only be 14 intervals.
For the above example the solution should look like....

ID Date Time Length
1 10/11/2014 9:00 30
1 10/11/2014 9:30 30
1 10/11/2014 10:00 30
1 10/11/2014 10:30 30
........for 14 intervals up until 16:00

Help!!!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-06 : 13:20:55

something like this:


declare @t table (id int, date_time datetime, [length] int, Interval_Num int)
insert into @t (id, date_time, [length], Interval_Num) values
(1,  '10/11/2014 9:00', 420, 14)

;with N0(n) as (select 1 from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) n(n)),
      N1(n) as (select 1 from N0, N0 N),
      N2(n) as (select 1 from N1, N1 N),
      N(n) as  (select row_number() over(order by (select null)) from N2)
select id, date_time, c.interval, 30
from @t
cross apply (
	select dateadd(minute, 30*(n-1), date_time)
	from N
	where n < Interval_Num
	) c(interval)

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-10-06 : 13:34:31

[code]declare @MyTable table (
id int,
DateTime datetime,
Length int,
Interval_Num int
)

insert into @MyTable
values(1, '10/11/2014 9:00', 420, 14)

-- select * from @MyTable

;with Intervals
as (
select ID, DateTime, Interval_Num
from @MyTable
where ID = 1 -- Set this value

union all

select ID, DateAdd(Minute, 30, DateTime), Interval_Num - 1
from Intervals
where Interval_Num >= 1
)
select ID, DateTime, 30 Length
from Intervals[/code]

No amount of belief makes something a fact. -James Randi

ertweety
Starting Member

15 Posts

Posted - 2014-10-06 : 13:56:47

Doesn't seem to work. I'm getting an invalid identifier for the dateadd function. Is this because I am using Oracle?

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-10-06 : 14:06:47

Its because you are using a Microsoft specific website to ask Oracle questions! I'm sure that there is an equivalent Oracle specific date arithmetic function but I have no idea what it would be...

No amount of belief makes something a fact. -James Randi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources