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,
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.