SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Rouding time values up to the nearest custon interval
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 11/29/2006 :  08:07:25  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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

Sweden
30108 Posts

Posted - 11/29/2006 :  08:51:33  Show Profile  Visit SwePeso's Homepage  Reply with Quote
-- 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
		

Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 11/29/2006 09:04:01
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000