Rouding time values up to the nearest custon interval

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

SQL Server Development (2000)

Rouding time values up to the nearest custon interval

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-11-29 : 08:07:25

David Mercer writes "I am trying to work out a function that will allow us to round a given time to the nearest custom interval.

I work in a hospital where we have consultant clinics. Take the example of a typical clinic where the session runs from 09:00 to 12:00 with each appointment lasting 30mins.

So to fit there must be an appointments starting at the following times

09:00
09:30
10:00
10:30
11:00
11:30

The problem is that within our data we have appointments for example at 10:05 and we want that time to say 10:30 instead which is the next appointment time after 10:05.

I have come up with a script that converts the times into seconds past midnight and does some rounding but then I get a bit stuck.

declare @starttime as int
declare @slottime as int
declare @appttime as int
declare @newtime as int

set @starttime = (cast(substring('12:05',1,2) as int) * 3600) + (cast(substring('12:05',4,2) as int) * 60)

set @appttime = (cast(substring('12:06',1,2) as int) * 3600) + (cast(substring('12:06',4,2) as int) * 60)

set @slottime = 5

set @newtime = @starttime + round(@starttime/@appttime,0) + @slottime

print @newtime

select 3600 * 6

print 43505 / 3600

print ((cast(43505 as decimal(10,0))/cast(3600 as decimal(10,0)))-round((43505/3600),0)) * 60

Can anyone suggest a script that will help me here?

David"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-29 : 08:51:33

[code]-- prepare test data
declare @test table (timeinfo datetime)

insert @test
select '10:05' union all
select '11:17' union all
select '12:30' union all
select '09:15' union all
select '10:00' union all
select '10:59'

declare @intervals table (minutes tinyint)

insert @intervals
select 5 union all
select 10 union all
select 15 union all
select 30 union all
select 45 union all
select 60 union all
select 90

-- do the work
select t.timeinfo,
i.minutes interval,
DATEADD(minute, (i.minutes - datediff(minute, 0, t.timeinfo) % i.minutes) % i.minutes, t.timeinfo) [nearest future time]
from @test t
cross join @intervals i
order by i.minutes,
t.timeinfo
[/code]
Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources