Post reply

SQL Query to subtract credit from Invoice Total

  • July 26, 2013 at 7:14 am

    #98824

    Hi everyone,

    Basically I am building a report to calculate commissions for our sales force. We pay commissions on invoiced amounts. It is easy to pull the information from the database, however when a credit memo (Invoice Number with CM on end) is issued against the Invoice it is added to the query (see example below) since it is registered as an -invoice. Is there a where to make the invoice amount less the credit memo amount? Also, if this equals zero to not display (this happens for all invoices that equal zero in the query, but not the ones canceled out by the query).

    InvoiceRecord Amount

    __________________________

    1001.......................$230.00

    1002.......................$135.56

    1003.......................$201.00 * Does not need to show on report

    1003CM...................$201.00 * Does not need to show on report

    1004.......................$134.00 * Report needs to show $135 - $35 = $100 <- show total

    1004CM...................$ 65.00 * Does not need to show on report

    Any help would be appreciated. Thanks in advance. Bill

  • July 26, 2013 at 7:28 am

    #1636150

    Should be straightforward from here, Bill:

    DROP TABLE #Invoices

    CREATE TABLE #Invoices (InvoiceNo VARCHAR(10), Amount DECIMAL(10,2))

    INSERT INTO #Invoices (InvoiceNo, Amount)

    SELECT '1001', $230.00 UNION ALL --

    SELECT '1002', $135.56 UNION ALL --

    SELECT '1003', $201.00 UNION ALL -- * Does not need to show on report

    SELECT '1003CM', $201.00 UNION ALL -- * Does not need to show on report

    SELECT '1004', $134.00 UNION ALL -- * Report needs to show $135 - $35 = $100 <- show total

    SELECT '1004CM', $65.00 -- * Does not need to show on report

    SELECT *

    FROM #Invoices i

    CROSS APPLY (

    SELECT Credits = SUM(Amount)

    FROM #Invoices c

    WHERE c.InvoiceNo = i.InvoiceNo+'CM'

    ) c

    WHERE RIGHT(InvoiceNo, 2) <> 'CM'

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • July 26, 2013 at 7:38 am

    #1636156

    Thank you very much... I will see what I can do. Bill

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply