Solved

T-Sql Converting One time value to another

Posted on 2014-01-27
2
305 Views
Last Modified: 2014-01-27
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.
0
Comment
Question by:Kyle Abrahams
2 Comments
 
LVL 68

Accepted Solution

by:
Qlemo earned 500 total points
ID: 39813041
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
 
LVL 39

Author Comment

by:Kyle Abrahams
ID: 39813638
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

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now