May 8, 2017 at 2:41 pm
Hi,
I'm trying to normalize my data to hours and I'm a bit confused.
For example, for 12 hours to 1 billed unit it would be:
sign(t1.AmountPaid) * abs(cast( t1.UnitsBilled as float) ) * 12.0
How would I write 1 hour to 4 billed units?
Thanks
May 8, 2017 at 3:48 pm
Briceston - Monday, May 8, 2017 2:41 PMHi,I'm trying to normalize my data to hours and I'm a bit confused.
For example, for 12 hours to 1 billed unit it would be:
sign(t1.AmountPaid) * abs(cast( t1.UnitsBilled as float) ) * 12.0How would I write 1 hour to 4 billed units?
Thanks
Could it be as simple as
sign(t1.AmountPaid) * abs(cast( t1.UnitsBilled as float) ) / 3.0
May 8, 2017 at 8:00 pm
DesNorton - Monday, May 8, 2017 3:48 PMBriceston - Monday, May 8, 2017 2:41 PMHi,I'm trying to normalize my data to hours and I'm a bit confused.
For example, for 12 hours to 1 billed unit it would be:
sign(t1.AmountPaid) * abs(cast( t1.UnitsBilled as float) ) * 12.0How would I write 1 hour to 4 billed units?
Thanks
Could it be as simple as
sign(t1.AmountPaid) * abs(cast( t1.UnitsBilled as float) ) / 3.0
Thanks for chimming in. Not sure I understand how you arrived at this?
May 8, 2017 at 10:09 pm
Briceston - Monday, May 8, 2017 8:00 PMDesNorton - Monday, May 8, 2017 3:48 PMCould it be as simple as
sign(t1.AmountPaid) * abs(cast( t1.UnitsBilled as float) ) / 3.0Thanks for chimming in. Not sure I understand how you arrived at this?
Based on the information available,
12H (hours) = 1BU (billed units) ... need 1H = 4BU
... Divide both sides by 12 to get to 1H, and multiply by 4 to get to 4BU
... 12H/12 = (1BU/12) *4
... 1H = 4/12BU
... 1H = 1/3BU
May 9, 2017 at 7:32 am
DesNorton - Monday, May 8, 2017 10:09 PMBriceston - Monday, May 8, 2017 8:00 PMDesNorton - Monday, May 8, 2017 3:48 PMCould it be as simple as
sign(t1.AmountPaid) * abs(cast( t1.UnitsBilled as float) ) / 3.0Thanks for chimming in. Not sure I understand how you arrived at this?
Based on the information available,
12H (hours) = 1BU (billed units) ... need 1H = 4BU
... Divide both sides by 12 to get to 1H, and multiply by 4 to get to 4BU
... 12H/12 = (1BU/12) *4
... 1H = 4/12BU
... 1H = 1/3BU
DesNorton - Monday, May 8, 2017 10:09 PMBriceston - Monday, May 8, 2017 8:00 PMDesNorton - Monday, May 8, 2017 3:48 PMCould it be as simple as
sign(t1.AmountPaid) * abs(cast( t1.UnitsBilled as float) ) / 3.0Thanks for chimming in. Not sure I understand how you arrived at this?
Based on the information available,
12H (hours) = 1BU (billed units) ... need 1H = 4BU
... Divide both sides by 12 to get to 1H, and multiply by 4 to get to 4BU
... 12H/12 = (1BU/12) *4
... 1H = 4/12BU
... 1H = 1/3BU
Thank you for providing the above example. Even though I provided the example : '12 hours to 1 billed', I don't think they are directly releated to form a computation.
it's two seperate question, but the data is derived from the same table. Maybe I'm missing something in the example you provided.
Here's the sample question:(IDCode XXXX is billed in 15 minutes increments, so 4 charged units is equal to 1 hour) The premise is to normalize units billed to hours.
Here 's a sample of the table content:
ID Code AmountPaid UnitsBilled
XXXX $0.00 1.00
XXXX $0.00 1.00
XXXX $0.00 3.00
XXXX $20.00 1.00
XXXX $0.00 24.00
May 9, 2017 at 8:00 am
If you are trying to update all your existing data to reflect that a unit of billable time is now 4 times longer than before, then the first task is to ensure that ALL billing computations reflect the now higher "per unit" rate, which should be 4 times higher than before. Once this is done, you can then update all the instances of "units billed" to 1/4 of their previous value, but understand, there's a certain distaste on the part of accountants for destroying your inputs, and that type of exercise could become problematic from an audit perspective. Understand also that values that may once have been stored as integers (and especially "units billed"), will no longer be possible to store as integers, so as to allow for data records representing less than a full unit. Not sure this whole exercise is a good idea, unless you are very early in the overall implementation and just need to change gears. If this is an existing system, I'd look solely at changing how it's reported. You'll still have a lot of similar decisions to make about how to represent partial units, and how to adjust the billing rate(s?), so there's a lot of thought that needs to go into this.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 9, 2017 at 8:34 am
sgmunson - Tuesday, May 9, 2017 8:00 AMIf you are trying to update all your existing data to reflect that a unit of billable time is now 4 times longer than before, then the first task is to ensure that ALL billing computations reflect the now higher "per unit" rate, which should be 4 times higher than before. Once this is done, you can then update all the instances of "units billed" to 1/4 of their previous value, but understand, there's a certain distaste on the part of accountants for destroying your inputs, and that type of exercise could become problematic from an audit perspective. Understand also that values that may once have been stored as integers (and especially "units billed"), will no longer be possible to store as integers, so as to allow for data records representing less than a full unit. Not sure this whole exercise is a good idea, unless you are very early in the overall implementation and just need to change gears. If this is an existing system, I'd look solely at changing how it's reported. You'll still have a lot of similar decisions to make about how to represent partial units, and how to adjust the billing rate(s?), so there's a lot of thought that needs to go into this.
sgmunson - Tuesday, May 9, 2017 8:00 AMIf you are trying to update all your existing data to reflect that a unit of billable time is now 4 times longer than before, then the first task is to ensure that ALL billing computations reflect the now higher "per unit" rate, which should be 4 times higher than before. Once this is done, you can then update all the instances of "units billed" to 1/4 of their previous value, but understand, there's a certain distaste on the part of accountants for destroying your inputs, and that type of exercise could become problematic from an audit perspective. Understand also that values that may once have been stored as integers (and especially "units billed"), will no longer be possible to store as integers, so as to allow for data records representing less than a full unit. Not sure this whole exercise is a good idea, unless you are very early in the overall implementation and just need to change gears. If this is an existing system, I'd look solely at changing how it's reported. You'll still have a lot of similar decisions to make about how to represent partial units, and how to adjust the billing rate(s?), so there's a lot of thought that needs to go into this.
Interesting take on this sgmunson. I appreciate your feedback. I'm only updating instances for a particular code. I'm updating to a separate table, not the origin table.
May 9, 2017 at 8:43 am
Briceston - Tuesday, May 9, 2017 7:32 AMHere's the sample question:(IDCode XXXX is billed in 15 minutes increments, so 4 charged units is equal to 1 hour) The premise is to normalize units billed to hours.Here 's a sample of the table content:
ID Code AmountPaid UnitsBilled
XXXX $0.00 1.00
XXXX $0.00 1.00
XXXX $0.00 3.00
XXXX $20.00 1.00
XXXX $0.00 24.00
OK. So in the sample data, each of the charged amounts is for 15 minutes, which is 1/4 hours.
So divide the UnitsBilled by 4 to get it in a per hour bases.
May 9, 2017 at 9:06 am
DesNorton - Tuesday, May 9, 2017 8:43 AMBriceston - Tuesday, May 9, 2017 7:32 AMHere's the sample question:(IDCode XXXX is billed in 15 minutes increments, so 4 charged units is equal to 1 hour) The premise is to normalize units billed to hours.Here 's a sample of the table content:
ID Code AmountPaid UnitsBilled
XXXX $0.00 1.00
XXXX $0.00 1.00
XXXX $0.00 3.00
XXXX $20.00 1.00
XXXX $0.00 24.00OK. So in the sample data, each of the charged amounts is for 15 minutes, which is 1/4 hours.
So divide the UnitsBilled by 4 to get it in a per hour bases.
DesNorton - Tuesday, May 9, 2017 8:43 AMBriceston - Tuesday, May 9, 2017 7:32 AMHere's the sample question:(IDCode XXXX is billed in 15 minutes increments, so 4 charged units is equal to 1 hour) The premise is to normalize units billed to hours.Here 's a sample of the table content:
ID Code AmountPaid UnitsBilled
XXXX $0.00 1.00
XXXX $0.00 1.00
XXXX $0.00 3.00
XXXX $20.00 1.00
XXXX $0.00 24.00OK. So in the sample data, each of the charged amounts is for 15 minutes, which is 1/4 hours.
So divide the UnitsBilled by 4 to get it in a per hour bases.
sign(t1.AmountPaid) * abs(cast( t1.UnitsBilled as float) ) / 4.0 ?
May 9, 2017 at 9:10 am
Briceston - Tuesday, May 9, 2017 9:06 AMDesNorton - Tuesday, May 9, 2017 8:43 AMOK. So in the sample data, each of the charged amounts is for 15 minutes, which is 1/4 hours.
So divide the UnitsBilled by 4 to get it in a per hour bases.sign(t1.AmountPaid) * abs(cast( t1.UnitsBilled as float) ) / 4.0 ?
Yes.
On another point, be careful of using FLOAT, as you lose could lose precision when casting back to a different data type. I would rather use DECIMAL(?,?). The scale and precision depend on your requirements.
May 9, 2017 at 9:11 am
Briceston - Tuesday, May 9, 2017 9:06 AMDesNorton - Tuesday, May 9, 2017 8:43 AMBriceston - Tuesday, May 9, 2017 7:32 AMHere's the sample question:(IDCode XXXX is billed in 15 minutes increments, so 4 charged units is equal to 1 hour) The premise is to normalize units billed to hours.Here 's a sample of the table content:
ID Code AmountPaid UnitsBilled
XXXX $0.00 1.00
XXXX $0.00 1.00
XXXX $0.00 3.00
XXXX $20.00 1.00
XXXX $0.00 24.00OK. So in the sample data, each of the charged amounts is for 15 minutes, which is 1/4 hours.
So divide the UnitsBilled by 4 to get it in a per hour bases.DesNorton - Tuesday, May 9, 2017 8:43 AMBriceston - Tuesday, May 9, 2017 7:32 AMHere's the sample question:(IDCode XXXX is billed in 15 minutes increments, so 4 charged units is equal to 1 hour) The premise is to normalize units billed to hours.Here 's a sample of the table content:
ID Code AmountPaid UnitsBilled
XXXX $0.00 1.00
XXXX $0.00 1.00
XXXX $0.00 3.00
XXXX $20.00 1.00
XXXX $0.00 24.00OK. So in the sample data, each of the charged amounts is for 15 minutes, which is 1/4 hours.
So divide the UnitsBilled by 4 to get it in a per hour bases.sign(t1.AmountPaid) * abs(cast( t1.UnitsBilled as float) ) / 4.0 ?
Sorry, I meant to write; sign(t1.AmountPaid) * abs(cast( t1.UnitsBilled as float) / 4) * 1.0
May 9, 2017 at 9:30 am
DesNorton - Tuesday, May 9, 2017 9:10 AMBriceston - Tuesday, May 9, 2017 9:06 AMDesNorton - Tuesday, May 9, 2017 8:43 AMOK. So in the sample data, each of the charged amounts is for 15 minutes, which is 1/4 hours.
So divide the UnitsBilled by 4 to get it in a per hour bases.sign(t1.AmountPaid) * abs(cast( t1.UnitsBilled as float) ) / 4.0 ?
Yes.
On another point, be careful of using FLOAT, as you lose could lose precision when casting back to a different data type. I would rather use DECIMAL(?,?). The scale and precision depend on your requirements.
For multiplication and division operations FLOAT will always provide better precision than any of DECIMAL types of the same binary length.
_____________
Code for TallyGenerator
May 9, 2017 at 1:04 pm
Sergiy - Tuesday, May 9, 2017 9:30 AMDesNorton - Tuesday, May 9, 2017 9:10 AMYes.On another point, be careful of using FLOAT, as you lose could lose precision when casting back to a different data type. I would rather use DECIMAL(?,?). The scale and precision depend on your requirements.
For multiplication and division operations FLOAT will always provide better precision than any of DECIMAL types of the same binary length.
I have had issues in the past with converting from int to float to decimal. This was a case of
Select an INT value from a table, pass it to a Proc as a parameter with FLOAT data type, which inserts into another table with a DECIMAL(18,4) data type.
This resulted in intermittent cases of values less than the original INT (eg: 10 would become 9.9999).
For this reason, I no longer use FLOAT in any of my code.
May 9, 2017 at 1:10 pm
Thank you all for chiming in. Much appreciated.
May 9, 2017 at 10:02 pm
DesNorton - Tuesday, May 9, 2017 1:04 PMI have had issues in the past with converting from int to float to decimal. This was a case of
Select an INT value from a table, pass it to a Proc as a parameter with FLOAT data type, which inserts into another table with a DECIMAL(18,4) data type.
This resulted in intermittent cases of values less than the original INT (eg: 10 would become 9.9999).For this reason, I no longer use FLOAT in any of my code.
The issue is not with FLOAT but with implicit conversions with no piece of mind.
And did not occur to you that the problem may be not with insufficient precision of FLOAT representation of INT, but with insufficient precision of DECIMAL representation of FLOAT?
Actual issue with precision appear when you do declare @I int
set @I = 10
select convert(decimal(19,4), @I) / 3.0 * 3.0 DecCalculation, convert(float, @I) / 3.0 * 3.0 FloatCalculationDecCalculation FloatCalculation
9.99999990 10
Now tell me how decimal data type provides better precision.
_____________
Code for TallyGenerator
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply