T-Sql Converting One time value to another

Hi All,

I have a requirement to tie records into billings based on a contract.  So for instance we may have a per minute rate based in the contract, but the value might be stored in seconds or hours.

I have a flag table (called timeIncrements) that has an ID and a name,

so

ID             Name
0      N/A
1      Second
2      Minute
3      Hour
4      Day
5      Week
6      Month
7      Year

I was thinking of adding a "To Next Level" definition (eg: Second would have 60 and hours would have 24) and then having a while loop that can go between them, but was wondering if someone might have something more elegant out there.

I'm thinking this should probably end up being a scalar function  
select convertTime(@value, @fromIncrement, @toIncrement)

with an output of something like:
select convertTime(3600, 1, 3)  -- 1
select convertTime(3600, 1, 4)  -- 1/24 = .0416
select convertTime(1, 3, 2) -- 60
select convertTime(1, 4, 2) -- 1440

Hope that makes sense.  Using  Sql 2008 R2.
LVL 41
Kyle AbrahamsSenior .Net DeveloperAsked:
Who is Participating?
 
QlemoConnect With a Mentor DeveloperCommented:
One way is to store an absolute factor in timeIncrements, e.g. based on seconds, and then just select those two factors and divide them.

E.g. timeIncrements:
ID   Name      Factor
0    N/A            1
1    Second         1
2    Minute        60
3    Hour        3600
4    Day        86400
5    Week      604800 
6    Month   18144000
7    Year   217728000

Open in new window

The result then is simply
@value * (select factor from timeIncrements where ID = @fromIncrement)
       / (select factor from timeIncrements where ID = @toIncrement)

Open in new window

0
 
Kyle AbrahamsSenior .Net DeveloperAuthor Commented:
create function fn_ConvertValueToTimeIncrement( @value decimal(18,6), @fromIncrement int, @toIncrement int)
returns decimal (18,6)
as

begin 

select @fromIncrement = (select factor from timeIncrements where Id = @fromIncrement) 

select @toIncrement =  (select factor from  timeIncrements where id = @toIncrement)
		


declare @result decimal(18,6)

-- my own error checking for static values  N/A was set to 0
if @fromIncrement = 0 or @toIncrement = 0
   set @result = 0
else if @fromIncrement = -1 or @toIncrement = -1
   set @result = @value
else 
   set @result = 
		( select
		   @value  			
		 * @fromIncrement
		 / @toIncrement
		)
		
				
return @result
  end

  
GO

Open in new window


That works well with no additional time added for the query.  (test on 58,300 rows) . . . 2 seconds to return results either way.  

Thanks.
0
All Courses

From novice to tech pro — start learning today.