ALLOCATION ISSUE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

ALLOCATION ISSUE

Author

Topic

pnpsql
Posting Yak Master

246 Posts

Posted - 2012-04-01 : 05:38:11

HI TEAM ,

THERE IS A CHARGE TABLE IN MY DATA BASE ,

CHARGE_TABLE : FOLLOWING ARE DESCRIPTION

AMOUNT TAX_PERCENT TAX_VALUE ETAX_PER ETAX_VALE
906.62 0.2 1.81 0.1 0.91

IN ABOVE SCRENARION THE TOTAL AMOUNT NEED TO BE PAID BY CUSTOMER
IN TWO INSTALLMENTS -> 906.62 + 1.81 + 0.91 = 909.34

NOW, WHEN WE USER PAY A CHARGE AMOUNT WE SATISFY THE CHARGE IN ANOTHER TABLE

LIKE :

AMOUNT_PAID TAX_PERCENT TAX_PAID ETAX_PER ETAX_PAID

453.31 0.2 0.91 0.1 0.45
453.31 0.2 0.91 0.1 0.45

BUT IN THIS CASE : THE ENTRIES IN TABLE ARE NOT CORRECT BECAUSE THE SUM OF
TAX_PAID = 0.91+0.91 = 1.82 IT SHOULD BE 1.81
AND ETAX_PAID = 0.45+0.45 = 0.90 IT SHOULD BE 0.91

I NEED TO CHECK BEFORE INSERT INTO TABLE THAT BOTH AMOUNT WILL BE CORRECT AND I NEED TO
ADD 0.1 IN ETAX_PAID AND LESS 0.1 IN TAX_PAID ..

PLEASE HELP....

challenge everything

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-01 : 08:08:37

There are two possibilities that I can think of:

a) The logic/arithmetic you use to calculate the payments and enter is incorrect. How are you calculating the taxes?

b) The data types you are using limits the number of decimal digits. What data types are you using? float? decimal?

It would help if you are able to post the DDL for the tables (which includes data types), some sample input data, and the expected output along with your queries. Take a look at Brett's blog here for some guidance: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

pnpsql
Posting Yak Master

246 Posts

Posted - 2012-04-01 : 08:17:34

the data type and logic is correct as per my knowledge, i need the logic to correct the charges , after calculation, i need to add in less amount and subtract from the excess amount. please help..

challenge everything

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-01 : 22:54:52

[code]
;with adj as
(
select c.pk,
amount_paid = a.amount_paid - c.amount,
tax_paid = a.tax_paid - c.tax_value,
etax_paid = a.etax_paid - c.etax_vale
from charge_table c
inner join
(
select pk,
amount_paid = sum(amount_paid),
tax_paid = sum(tax_paid),
etax_paid = sum(etax_paid)
from another_table
group by pk
) a on c.pk = a.pk
)
update c
set amount_paid = c.amount_paid - a.amount_paid,
tax_paid = c.tax_paid - a.tax_paid,
etax_paid = c.etax_paid - a.etax_paid
from (
select *, rn = row_number() over( partition by pk order by amount_paid )
from another_table
) c
inner join adj a on c.pk = a.pk
where c.rn = 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources