Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2003
    Location
    Johannesburg, South Africa
    Posts
    28

    Unanswered: 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

    JON

  2. #2
    Join Date
    Oct 2003
    Location
    Dallas
    Posts
    76

    you could try something like this....

    select 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

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    How is this different from your first post?

    -PatP

  4. #4
    Join Date
    Oct 2003
    Location
    Dallas
    Posts
    76

    what the heck are you talking about

    ?????????

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54

    Re: what the heck are you talking about

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

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •