March 24, 2017 at 9:29 am
Hello,
I need help to summarise different types of costs of invoice in a different way.
The story
1) Invoices may have up to three different elements of costs: product / service cost, tax and VAT.
2) Our system allows different payment options e.g. by Credit Card, Direct payment or via Account.
3) Our new customer, in some cases, demands to charge VAT by Credit Card, yet the rest (product cost and tax) will be charged, based on the original payment type (either Direct or Account).
4) In addition to item 3) this customer also demands to print every Direct / Account record on the invoice separately, but to summarise all Credit Card lines in one.
Invoice example
The example below has four records: two paid using Direct option and two- using Credit Card. Our table has a relevant logical column Add VAT to CC.
IF OBJECT_ID('tempdb..#costs') IS NOT NULL
DROP TABLE #costs;
CREATE TABLE #costs (
invoice_number INT,
invoice_line SMALLINT,
payment_method VARCHAR(20),
cost_value NUMERIC(16,6),
tax_value NUMERIC(16,6),
vat_value NUMERIC(16,6),
add_vat_to_credit_card BIT);
WITH mycosts AS (
SELECT 100 AS invoice,
1 AS line,
'Direct' AS payment,
20391 AS cost,
17627 AS tax,
295.67 AS vat,
1 AS add_vat_to_credit_card
union all
SELECT 100 AS invoice,
2 AS line,
'Direct' AS payment,
20391 AS cost,
17627 AS tax,
295.67 AS vat,
1 AS add_vat_to_credit_card
union all
SELECT 100 AS invoice,
3 AS line,
'Credit Card' AS payment,
300 AS cost,
0 AS tax,
0 AS vat,
0 AS add_vat_to_credit_card
union all
SELECT 100 AS invoice,
4 AS line,
'Credit Card' AS payment,
300 AS cost,
0 AS tax,
0 AS vat,
0 AS add_vat_to_credit_card
)
INSERT INTO #costs
SELECT * FROM mycosts;
SELECT * FROM #costs;
Requirement
The requirement in this example is to show only THREE lines:
1. 38018 (20391+17627)
2. 38018 (20391+17627)
3. 1191.34 (295.67 x 2 + 300 x 2)
What I have done
The best closest solution I was able to write is below. It shows me all Direct records as required and also repeat same Credit Card record twice with the same amount, so I only need to pick any record.
SELECT invoice_number,invoice_line,payment_method,
CASE payment_method WHEN 'Credit Card'
THEN NULL
ELSE cost_value + tax_value +
CASE add_vat_to_credit_card WHEN 0
THEN vat_value
ELSE 0
END
END AS non_CC_total,
CASE payment_method WHEN 'Credit Card'
THEN SUM(CASE add_vat_to_credit_card WHEN 1
THEN vat_value
ELSE 0
END) OVER (PARTITION BY invoice_number) +
SUM(CASE payment_method WHEN 'Credit Card'
THEN cost_value + tax_value + vat_value
ELSE 0
END) OVER (PARTITION BY invoice_number)
END AS Credit_Card_Costs
FROM #costs;
I am pretty sure there is an option to have it in a much more elegant way, perhaps using dimensions, but I do not know how. Can one advise, please?
Much appreciated
March 24, 2017 at 9:47 am
This?SELECT C.cost_value + C.tax_value AS Payment_value
FROM #costs C
WHERE C.payment_method = 'Direct'
UNION ALL
SELECT SUM(CASE C.payment_method WHEN 'Credit Card' THEN C.cost_value + C.tax_value
WHEN 'Direct' THEN vat_value END) AS Payment_value
FROM #costs C;
P.s. Thank you for both DDL and sample data, and even expected output. You even included your attempts. It was so refreshing to see a nice properly formatted post :w00t:
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 24, 2017 at 9:51 am
Yes, thank you.
But is it OK, from performance point of view, to call the same table twice, please? The actual table has 16M records.
March 24, 2017 at 10:05 am
Will the result set always be for one individual or Invoice? If so, provided that your indexes are in the right place, it not going to make a huge difference, AS you won't be summing16M rows, but only a few
Have you tested it to see how quick it is?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 24, 2017 at 10:52 am
The result set will be per invoice, but not all Direct payments will have TRUE in their Add VAT to CC column. Some may have FALSE ,e.g. product's insurance.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply