The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.
|
 |
|
Dev Shed Forums
> Databases
> MS SQL Development
|
Query rounding errors and compensation
Discuss Query rounding errors and compensation in the MS SQL Development forum on Dev Shed. Query rounding errors and compensation MS SQL Development forum discussing administration, MS SQL queries, and other MS SQL-related topics. SQL Server is Microsoft's enterprise database engine.
|
|
 |
|
|
|
|

Dev Shed Forums Sponsor:
|
|
|

December 3rd, 2003, 09:01 AM
|
|
Registered User
|
|
Join Date: Aug 2003
Posts: 11
Time spent in forums: < 1 sec
Reputation 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 09:04 AM.
|

December 3rd, 2003, 10:18 AM
|
|
Registered User
|
|
Join Date: Aug 2003
Posts: 11
Time spent in forums: < 1 sec
Reputation 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
|
Developer Shed Advertisers and Affiliates
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
Rate This Thread |
Linear Mode
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|