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.
Author 
Topic 
pnpsql
Posting Yak Master
246 Posts 
Posted  20120401 : 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  20120401 : 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  20120401 : 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  20120401 : 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] 




