[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

T-Sql Converting One time value to another

Posted on 2014-01-27
2
Medium Priority
?
316 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 71

Accepted Solution

by:
Qlemo earned 2000 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 41

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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

650 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