June 7, 2018 at 6:30 am
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?
June 7, 2018 at 1:46 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
June 7, 2018 at 3:30 pm
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?
June 7, 2018 at 3:41 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?
June 7, 2018 at 6:23 pm
Matt Miller (4) - Thursday, June 7, 2018 3:30 PMcase 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