Tuesday, March 27, 2012

Comparing result set values of 2 queries ?

Any assistance would be so helpful !!

We have 2 tables.. lets call them INV and COST

Table INV and COST have 3 related columns, namely ID,AMOUNT and VAT. As shown below...

ID | AMOUNT | VAT ( INV TABLE )
1 |20.125 |2.896
2 |10.524 |1.425

ID | AMOUNT | VAT ( COST TABLE )
1 |20.125 |4.821 ... different to ID 1 in INV Table
2 |10.524 |1.425

If you look above, I need to sum the AMOUNT and VAT columns and get a value for each ID, then compare the two tables and get the ID's that have different values...in this case I would need a result saying ID1 as the total of INV TABLE ID1 (23.021) is different to the corresponding ID1 row in COST TABLE (24.946)

Thats it ?

Please could someone out there offer some ideas ?

THANKS

JONselect id, sum(amount) as inv_amount, sum(vat) as inv_vat,
cast(0, decimal(11,3)) as cost_amount, cast(0, decimal(11,3)) as cost_vat
into #inv
from INV
group by id

select id, sum(amount) as cost_amount, sum(vat) as cost_vat
into #cost
from cost
group by id

update x
set x.cost_amount = v.cost_amount,
x.cost_vat = v.cost_vat
from #inv x, #cost v
where x.id = v.id

select * from #inv|||How is this different from your first post (http://www.dbforums.com/t994762.html)?

-PatP|||???|||Originally posted by mkkmg
??? Click the link I posted. This isn't the first time they've posted that question.

-PatPsqlsql

No comments:

Post a Comment