SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 ALLOCATION ISSUE
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pnpsql
Posting Yak Master

India
246 Posts

Posted - 04/01/2012 :  05:38:11  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 04/01/2012 :  08:08:37  Show Profile  Reply with Quote
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
Go to Top of Page

pnpsql
Posting Yak Master

India
246 Posts

Posted - 04/01/2012 :  08:17:34  Show Profile  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17595 Posts

Posted - 04/01/2012 :  22:54:52  Show Profile  Reply with Quote

;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



KH
Time is always against us

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000