#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Posts
    3
    Rep Power
    0

    Question SQL - summing invoice items and taxes


    I have three tables - Invoice, InvoiceItem and InvoiceItemTax, all related by InvoiceNumber. InvoiceItemTax may no exist for a given InvoiceNumber.

    On Invoice is a denormalized TotalAmount column.

    On InvoiceItem is a TotalPrice
    On InvoiceTax is a TotalTax

    I want to do an integrity check to ensure the data is accurate for all invoices.

    I want to ensure the Invoice.TotalAmount equals the sum of all the related InvoiceItem.TotalPrice PLUS the sum of all the related InvoiceTax.TotalTax

    I can't figure out how to do this!

    all help appreciated.
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Location
    Canada - Egypt
    Posts
    60
    Rep Power
    12
    I hope I didn't mis-understand what you ment.
    but if you want a query that would report improper values of total amount then:

    In ACCESS this should work

    a query like this should give you the results you want

    SELECT ID
    FROM [IN]
    WHERE TAmount <> (
    (Select sum( Price )
    FROM [Item]
    Where [Item].ID = [in].ID)
    +
    (Select sum (TAx)
    FROM [Tax]
    Where [Tax].ID = [in].ID)
    )


    For
    Table IN -- Fields ID, TAmout
    Table TAx -- Fields ID, TaxAmount
    Table Item -- Fields ID, Price

    where x is the ID of the invoice you want to validate.
    The IDs returned are for invoices that do not pass the validation.

    The syntax might be a little different in other DB platforms.

    Note: I have only tested this using very small data sets .. so it might not be 100% effecient, please make sure you test more.
    Last edited by yassoor; February 19th, 2003 at 04:09 AM.
    I hope this is of any help to anyone.

    Yassoor
    http://www.WebsitesCreation.ca
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Posts
    3
    Rep Power
    0
    Thanks for the info. In fact, I am doing this in Oracle and i have tried the following SQL:

    SELECT *
    FROM inv a
    WHERE total_amt <>
    ((Select sum( qty_num * unit_price ) FROM inv_item b Where a.inv_no = b.inv_no)
    +
    (Select sum (tax_amt) FROM inv_item_tax c Where a.inv_no = c.inv_no=));

    It still does not work - I get a 'missing right parenthesis' error on at the + sign.

    I've tried lots of combinations, but can't get around the error.
  6. #4
  7. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Location
    Canada - Egypt
    Posts
    60
    Rep Power
    12
    IN
    SELECT *
    FROM inv a
    WHERE total_amt <>
    ((Select sum( qty_num * unit_price ) FROM inv_item b Where a.inv_no = b.inv_no)
    +
    (Select sum (tax_amt) FROM inv_item_tax c Where a.inv_no = c.inv_no=));

    Is the very last = sign a typo?!
    I hope this is of any help to anyone.

    Yassoor
    http://www.WebsitesCreation.ca
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Posts
    3
    Rep Power
    0
    Yes - thats a typo.

IMN logo majestic logo threadwatch logo seochat tools logo