August 9, 2018 at 3:22 am
I need to return payments records from a same table, which must be organised as following:
1) All Credit Cards payments must be grouped by
2) All other types of payments should be presented as they are in the payments table.
I simply achieve it by having a function with the working example below.
My question if this the correct approach - to query the table twice, or perhaps there is something better, please?
Thank you
WITH payments AS
(SELECT 100.00 AS Ex_VAT,
20.00 AS VAT,
120.00 AS Total,
'Credit Card' AS Payment_Method
UNION ALL
SELECT 123.45,
12.34,
135.89,
'Credit Card'
UNION ALL
SELECT 987.65,
43.21,
1234.56,
'Direct'
UNION ALL
SELECT 564.98,
98,
875,
'Direct'
UNION ALL
SELECT 1.98,
0.98,
8.75,
'Other'
)
SELECT SUM(p.Ex_VAT) AS Ex_VAT,
SUM(p.VAT) AS VAT,
SUM(Total) AS Total,
Payment_Method
FROM payments p
WHERE p.Payment_Method = 'Credit Card'
GROUP BY Payment_Method
UNION ALL
SELECT p.Ex_VAT,
p.VAT,
p.Total,
p.Payment_Method
FROM payments p
WHERE p.Payment_Method != 'Credit Card'
ORDER BY p.Payment_Method;
August 9, 2018 at 3:51 am
This will ready your table only once, instead of twice as your query does. I think there'll be extra sort operations, though, so you'll want to test whether the IO you've saved makes up for that. I imagine somebody will be able to come up with something that reads the table only once and does less sorting.
WITH payments AS
(SELECT 100.00 AS Ex_VAT,
20.00 AS VAT,
120.00 AS Total,
'Credit Card' AS Payment_Method
UNION ALL
SELECT 123.45,
12.34,
135.89,
'Credit Card'
UNION ALL
SELECT 987.65,
43.21,
1234.56,
'Direct'
UNION ALL
SELECT 564.98,
98,
875,
'Direct'
UNION ALL
SELECT 1.98,
0.98,
8.75,
'Other'
)
, Grouped AS (
SELECT
Ex_VAT
, VAT
, Total
, Payment_Method
, CASE Payment_Method
WHEN 'Credit Card' THEN 'Credit Card'
ELSE Payment_Method + CAST(NEWID() AS char(36))
END AS GroupedPaymentMethod
FROM payments
)
SELECT DISTINCT
SUM(p.Ex_VAT) OVER (PARTITION BY GroupedPaymentMethod) AS Ex_VAT,
SUM(p.VAT) OVER (PARTITION BY GroupedPaymentMethod) AS VAT,
SUM(Total) OVER (PARTITION BY GroupedPaymentMethod) AS Total,
Payment_Method
FROM Grouped p;
John
August 9, 2018 at 10:01 am
It's hard to tell on such a small sample, but using GROUPING SETS might actually perform better.
WITH payments AS
(SELECT 100.00 AS Ex_VAT,
20.00 AS VAT,
120.00 AS Total,
'Credit Card' AS Payment_Method
UNION ALL
SELECT 123.45,
12.34,
135.89,
'Credit Card'
UNION ALL
SELECT 987.65,
43.21,
1234.56,
'Direct'
UNION ALL
SELECT 564.98,
98,
875,
'Direct'
UNION ALL
SELECT 1.98,
0.98,
8.75,
'Other'
)
, Payments_ID AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY p.Payment_Method ORDER BY @@VERSION) AS Pay_ID,
p.Payment_Method,
p.Ex_VAT,
p.Total,
p.VAT
FROM payments p
)
SELECT SUM(p.Ex_VAT) AS Ex_VAT,
SUM(p.VAT) AS VAT,
SUM(p.Total) AS Total,
p.Payment_Method
FROM Payments_ID AS p
GROUP BY GROUPING SETS((p.Payment_Method, p.Pay_ID), (p.Payment_Method))
HAVING
( Payment_Method <> 'Credit Card' AND GROUPING(Pay_ID) = 0 )
OR ( Payment_Method = 'Credit Card' AND GROUPING(Pay_ID) = 1 )
ORDER BY Payment_Method
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 9, 2018 at 10:54 am
drew.allen - Thursday, August 9, 2018 10:01 AMIt's hard to tell on such a small sample, but using GROUPING SETS might actually perform better.
yes, the result set is per invoice and will be quite small - circa 10 records.
thank you for the suggestion.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply