#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    11
    Rep Power
    0

    Query rounding errors and compensation


    Kind of new to SQL so let me set this up a bit.
    I am writing a month end billing report in query analyzer for orders that have invoices processed within the date range. The results are then being sent to a file.
    For every order there can be up to 5 account numbers billed based on an allocated percentage of the invoiced amount for that account number. The hitch is since Iím dealing with percentages there are occasionally rounding errors.

    How can I add the calculated totals and compare them with the invoice amount, if there is a difference then add/subtract the amount to the calculated total with the highest percent?

    Iím pulling from two tables Orders and Invoices.

    ďOrdersĒ fields: Acct1, Pct1, Acct2, Pct2, Acct3, Pct3, Acct4, Pct4, Acct5, Pct5, Order_Nbr

    "Invoices" fields: Invoice_Timestamp, Invoice_Amt, Order_Nbr

    There are 5 selects unioníd together like this, but to save space Iíll only show the first 2.
    @StartDate = first of the month @MyDate=last day of the month
    -----------------------------------------------------------------------------------

    SELECT
    CAST(h.Acct1 AS CHAR(6))
    + CAST(k.Order_Nbr AS CHAR(6))
    + Replace((Replace(CAST(k.Invoice_Amt * (h.Pct1/100) as DECIMAL(6,2)),'.','')),'-','')
    FROM Invoices AS k
    left join Orders AS h ON k.Order_Nbr = h.Order_Nbr

    WHERE k. Invoiced_Timestamp <= @MyDate and
    k. Invoiced_Timestamp >= @MyStartDate and
    k. Invoice_Amt <> '0' and
    h.Acct1 <> ''

    UNION ALL SELECT
    CAST(h.Acct2 AS CHAR(6))
    + CAST(k.Order_Nbr AS CHAR(6))
    + Replace((Replace(CAST(k.Invoice_Amt * (h.Pct2/100) as DECIMAL(6,2)),'.','')),'-','')

    FROM Invoices AS k
    left join Orders AS h ON k.Order_Nbr = h.Order_Nbr

    WHERE k. Invoiced_Timestamp <= @MyDate and
    k. Invoiced_Timestamp >= @MyStartDate and
    k. Invoice_Amt <> '0' and
    h.Acct2 <> ''

    Thanks in advance for any help.
    Last edited by friendly_state; December 3rd, 2003 at 10:04 AM.
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    11
    Rep Power
    0
    I am still interested in any ideas, but what I decided to do

    add two fields to the Invoice table
    Adjustment_Acct = 1, 2, 3, 4,or 5
    Adjustment_Amt = difference in calculated and actual

    I'll calculate and insert these when invoices are entered. Then when I'm running the billing statement I can do case statements for each acct. Like this

    ***Adjust for first account****
    Case (when k.Adjustment_Acct = 1) then
    Replace((Replace(CAST((k.Invoice_Amt * (h.Pct1/100) + k.Adjustment_Amt) as DECIMAL(6,2)),'.','')),'-','')
    Else
    Replace((Replace(CAST((k.Invoice_Amt * (h.Pct1/100) as DECIMAL(6,2)),'.','')),'-','')
    End

IMN logo majestic logo threadwatch logo seochat tools logo