Reverse engineering a duty rate when dealing with small values

Question

Reverse engineering a duty rate when dealing with small values

jeffshelix

SSCrazy

Points: 2257
More actions
March 23, 2018 at 9:05 am

#334069

I need to compute the Duty Rate on an item by dividing the Duty Paid by the Value of the goods.
and this code works:
CASE when [VALUE] =0 THEN 0 ELSE Round(CAST([Duty] AS float) / CAST([Value] AS float),3) END as [Duty Rate]

But, if the value is low, then i get incorrect duty rates . . .

HTS_Num	Duty Rate	VALUE	DUTY
3923500000	0.055	0.91	0.05
3923500000	0.049	2.24	0.11
3923500000	0.046	2.4	0.11
3923500000	0.049	4.28	0.21
3923500000	0.052	12.35	0.64
.053 is corrrect	0.053	90.29	4.77
.053 is corrrect	0.053	96.74	5.14

Is there a different SQL i can use to get those first 6 rows to come out as .053 ???

thanks

ScottPletcher SSC Guru Points: 100948 More actions · Answer 1

I don't believe there is, since the value is rounded up. You can calc the rate that would produce the rounded-up amount, but I can't see how you would ever know if/how much the final duty amount reflected rounding.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

jeffshelix SSCrazy Points: 2257 More actions · Answer 2

jeffshelix

SSCrazy

Points: 2257

March 23, 2018 at 12:07 pm

#1984444

thanks, but do i HAVE TO round?????

ScottPletcher SSC Guru Points: 100948 More actions · Answer 3

All taxing authorities I know of at least allow rounding of tax amounts, and afaik all businesses do it. After all, it's ultimately extra money to them to charge more tax.

With a rate like 0.053, it's obviously the result will not always be an even penny, in which case it gets rounded up. Heaven knows the govt can't give up all those pennies!

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

jeffshelix SSCrazy Points: 2257 More actions · Answer 4

Agreed . . . their concern is that if it isnt ".053" on a dollar part, why would it be it be ".053" on a million dollar part . . .
We do have tables that tell us what the actual rate is/was . . . but we are going back 5 years, and those duty rates can change twice a year . . . . and matching up the correct rate to the actual entry date is a nightmare.

THANKS, I can consider this SOLVED . . .

sgmunson SSC Guru Points: 110639 More actions · Answer 5

jeffshelix - Friday, March 23, 2018 1:49 PM
Agreed . . . their concern is that if it isnt ".053" on a dollar part, why would it be it be ".053" on a million dollar part . . .
We do have tables that tell us what the actual rate is/was . . . but we are going back 5 years, and those duty rates can change twice a year . . . . and matching up the correct rate to the actual entry date is a nightmare.
THANKS, I can consider this SOLVED . . .

Yeah, that's no easy task. Unfortunately, the best you could possibly do is AVERAGE a large number of computations, and your "computed rate" would "improve " over time, as it converges on the actual rate. Better to have someone that maintains a table with rates and effective dates, simplifying the task of finding the right rate, but that's not always practical.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)