How to spread an invoice tax across invoice lines without loosing rounded remainder in a view?

  • I have a view, which shows Invoice Details table. This view is used to print invoices to the customers.
    In my case each record of Invoice Details is a passenger, so for three passengers, as in my example below, I will have three Invoice Details records in the view. All perfect.
    Now I have a request to also reflect tax costs, distributed over the passengers. For example if the invoice has a tax of £9.00 each of the records will show £3.00, which is perfect: £3.00 x 3 = £9.00.
    My trouble as per example below, how to address taxes, which cannot be evenly divided across the passengers, e.g. £10.00 divided amongst three records will show on the invoice  £3.33, but £3.33 x 3 = £9.99,
    so the customer will receive a printed invoice with total tax of £10.00, but each of the records will show only £3.33

    Presently I address the issue in our application by accumulation per record and then by adding a difference to the last one: IF current_line = total_number_of_lines THEN £10.00 - £3.33 x 3 ELSE 0

    I wonder if it can be easily achieved in SQL as well, please?

    IF OBJECT_ID('tempdb..#list_of_pax') IS NOT NULL
    DROP TABLE #list_of_pax;
    GO

    DECLARE @v_tax_per_invoice NUMERIC(10,2) = 10.00;

    SELECT * INTO #list_of_pax
    FROM (
    SELECT 10             AS invoice_number,
            1             AS invoice_line,
         'SMITH/JOHNMR' AS pax_name,
         95.20         AS ticket_cost_without_GST,
         5.00             AS ticket_cost_GST,
         100.20         AS ticket_cost_total
    UNION ALL
    SELECT 10             AS invoice_number,
            2             AS invoice_line,
            'SMITH/SARAHDR' AS pax_name,
         95.20         AS ticket_cost_without_GST,
         5.00             AS ticket_cost_GST,
         100.20         AS ticket_cost_total
    UNION ALL
    SELECT 10             AS invoice_number,
            3             AS invoice_line,
            'JOHNSON/MAIRAMRS' AS pax_name,
         95.20         AS ticket_cost_without_GST,
         5.00             AS ticket_cost_GST,
         100.20         AS ticket_cost_total) l

    SELECT    l.pax_name,
            l.ticket_cost_without_GST,
            l.ticket_cost_GST,
            l.ticket_cost_total,
            ROUND(@v_tax_per_invoice /
            COUNT(l.pax_name) OVER (PARTITION BY l.invoice_number),2) AS tax_per_pax
    FROM #list_of_pax l

    I wonder if it can b how it can be addressed in SQL, please?

  • Not easily, no.  With the current query,for example, we have no way of knowing which row is the last pax.

    Btw, you should truncate not round, or you will have a worse problem when the value rounds up (such as 20.0 / 3 instead of 10.0 / 3):

    ROUND(@v_tax_per_invoice /
    COUNT(l.pax_name) OVER (PARTITION BY l.invoice_number),2,-1) AS tax_per_pax

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • I just had to so something similar for an invoicing system:


    With Pax_orderingCTE as (


    select Row_number() over (partition by invoice_number order by invoice_line desc) RN, --change this to determin your "last line"


    l.invoice_number,


    l.pax_name,


    l.ticket_cost_without_GST,


    l.ticket_cost_GST,


    l.ticket_cost_total,


    ROUND(@v_tax_per_invoice /


    COUNT(l.pax_name) OVER (PARTITION BY l.invoice_number),2) AS tax_per_pax


    FROM #list_of_pax l)


    select *, tax_per_pax+ case when RN=1 then @v_tax_per_invoice -sum(tax_per_pax) over (partition by invoice_number) else 0 end adjustforrounding


    from Pax_orderingCTE

    Pick your favorite convention for determine "last passenger" (I took that to mean last on the same invoice).

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Matt Miller (4) - Thursday, June 7, 2018 3:30 PM

    case when RN=1 then @v_tax_per_invoice -sum(tax_per_pax) over (partition by invoice_number) else 0 end adjustforrounding

    Thank you for the suggestion - it does work. I was playing with LEAD though, as I would think it will be quicker, no?

  • BOR15K - Thursday, June 7, 2018 3:41 PM

    Matt Miller (4) - Thursday, June 7, 2018 3:30 PM

    case when RN=1 then @v_tax_per_invoice -sum(tax_per_pax) over (partition by invoice_number) else 0 end adjustforrounding

    Thank you for the suggestion - it does work. I was playing with LEAD though, as I would think it will be quicker, no?

    I don't know for a fact.  That said - LEAD is usually for various forms of running totals over multiple related records using some predictable order, which seems to be overkill for what it is you need here.  Still might be worth trying with a decent sized-data set realistic for your needs.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Viewing 5 posts - 1 through 4 (of 4 total)

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