May 30, 2013 at 12:11 pm
A master table contains a series of invoices and a detail table comprise, for any invoice, 1 or 2 or 3 ... payments for the invoice and an invoice also might have no payments.
Obviously, if an invoice has multiple payments, the LEFT OUTER JOIN between the master and detail table will produce multiple records for the same invoice master. So the sum of the Amounts includes several repetition of the same invoice amount. And, of course, two distinct invoices may have the same amount.
Been racking my brains to fabricate a SINGLE T-SQL that could simultaneously create the sum of each invoice amount and the sum of all the payments. These results have to be broken down by currency.
Any ideas ?
This is the required result:
[font="Courier New"]/*
GrandTotal Desired is the correct value, GrandTotal Obtained is wrong
Currency GrandTotal GrandTotal Payments
Desired Obtained
EUR 15346.00 21349.00 800.80
USD 6134.00 7135.00 667.3334
*/[/font]
Here are table creation and filling scripts and the obviously failed LEFT OUTER JOIN
[font="Courier New"]BEGIN TRAN
CREATE TABLE #INV
(
InvNo int NOT NULL IDENTITY,
Amount money,
Currency CHAR(3)
)
CREATE TABLE #Pay
(
PayNo int NOT NULL IDENTITY(100,1),
InvNo int NOT NULL,
Paid money
)
INSERT INTO #INV (Amount, Currency)
SELECT 1001, 'USD' UNION
SELECT 1011, 'USD' UNION
SELECT 1111, 'USD' UNION
SELECT 2001, 'EUR' UNION
SELECT 2011, 'EUR' UNION
SELECT 2111, 'EUR' UNION
SELECT 3001, 'EUR' UNION
SELECT 3011, 'USD' UNION
SELECT 3111, 'EUR' UNION ALL
SELECT 3111, 'EUR'
INSERT INTO #Pay (InvNo, Paid) SELECT TOP 1 InvNo, CONVERT(money, Amount/3.00) FROM #INV
INSERT INTO #Pay (InvNo, Paid) SELECT TOP 1 InvNo, CONVERT(money, Amount/3.00) FROM #INV
INSERT INTO #Pay (InvNo, Paid) SELECT TOP 1 InvNo+3, CONVERT(money, Amount/5.00) FROM #INV
INSERT INTO #Pay (InvNo, Paid) SELECT TOP 1 InvNo+3, CONVERT(money, Amount/5.00) FROM #INV
INSERT INTO #Pay (InvNo, Paid) SELECT TOP 1 InvNo+3, CONVERT(money, Amount/5.00) FROM #INV
INSERT INTO #Pay (InvNo, Paid) SELECT TOP 1 InvNo+3, CONVERT(money, Amount/5.00) FROM #INV
SELECT * FROM #INV
SELECT * FROM #Pay
SELECT * FROM #INV LEFT OUTER JOIN #Pay ON #Pay.InvNo = #INV.InvNo
SELECT #INV.Currency, SUM(#INV.Amount) AS GrandTotal
FROM #INV
GROUP BY #INV.Currency
SELECT #INV.Currency, SUM(#PAY.Paid) AS Payments
FROM #Pay INNER JOIN #INV ON #Pay.InvNo = #INV.InvNo
GROUP BY #INV.Currency
SELECT #INV.Currency, SUM(#INV.Amount) AS GrandTotal, SUM(#PAY.Paid) AS Payments
FROM #INV LEFT OUTER JOIN #Pay ON #Pay.InvNo = #INV.InvNo
GROUP BY #INV.Currency
ROLLBACK TRAN[/font]
May 30, 2013 at 12:46 pm
Great job posting ddl and sample data. I can understand GrandTotalDesired and Payments. However, what is the logic for GrandTotal Obtained? Can you explain what that column contains?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 30, 2013 at 12:55 pm
Will this do what you want? (Place directly before your rollback statement)
SELECT
Inv.Currency
, SUM(Inv.Amount) AS [GrandTotal]
, SUM(coalesce(Pay.Paid, 0)) AS [Payments]
FROM #INV Inv
LEFT OUTER JOIN
(
SELECT
InvNo
, SUM(Paid) AS [Paid]
FROM #Pay
GROUP BY
InvNo
) Pay ON Pay.InvNo = Inv.InvNo
GROUP BY
Inv.Currency
blog: http://www.codemuddler.com
twitter: http://www.twitter.com/CodeMuddler
May 30, 2013 at 1:04 pm
Here are a couple of other ways to get the same results. One uses a cte and the second instead uses a cross apply.
--CTE Version
with GrandTotal as
(
SELECT SUM(Amount) as GrandTotalDesired, Currency FROM #INV group by Currency
)
SELECT i.Currency, SUM(Paid) as AmountPaid, gt.GrandTotalDesired
FROM #INV i
join #Pay p on i.InvNo = p.InvNo
join GrandTotal gt on gt.Currency = i.Currency
group by i.Currency, gt.GrandTotalDesired
--CROSS APPLY Version
SELECT i.Currency, SUM(Paid) as AmountPaid, gt.GrandTotalDesired
FROM #INV i
join #Pay p on i.InvNo = p.InvNo
cross apply (SELECT SUM(Amount) as GrandTotalDesired, Currency FROM #INV group by Currency) gt
where gt.Currency = i.Currency
group by i.Currency, gt.GrandTotalDesired
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 30, 2013 at 1:04 pm
SQL_FS: And you are still a "Grasshopper" ?
Yes, it works, thanks for a speedy reply too.:-)
May 30, 2013 at 1:15 pm
Sean Lange:
Actually, the following SQL
[font="Courier New"]
SELECT #INV.InvNo, #INV.Currency, #INV.Amount AS GrandTotal, #PAY.Paid
FROM #INV LEFT OUTER JOIN #Pay ON #Pay.InvNo = #INV.InvNo
ORDER BY #INV.Currency, #INV.InvNo
[/font]
Produces the following results set: Note that invoices having multiple payments are listed more than once.
So sum(Amount) includes the repeated invoices multiple times, yielding the wrong total. This was the "obtained" column.
[font="Courier New"]
InvNoCurrencyGrandTotalPaid
4 EUR 2001.00 200.20
4 EUR 2001.00 200.20
4 EUR 2001.00 200.20
4 EUR 2001.00 200.20
5 EUR 2011.00 NULL
6 EUR 2111.00 NULL
7 EUR 3001.00 NULL
9 EUR 3111.00 NULL
10 EUR 3111.00 NULL
1 USD 1001.00 333.6667
1 USD 1001.00 333.6667
2 USD 1011.00 NULL
3 USD 1111.00 NULL
8 USD 3011.00 NULL
[/font]
May 30, 2013 at 1:23 pm
j-1064772 (5/30/2013)
Sean Lange:Actually, the following SQL
[font="Courier New"]
SELECT #INV.InvNo, #INV.Currency, #INV.Amount AS GrandTotal, #PAY.Paid
FROM #INV LEFT OUTER JOIN #Pay ON #Pay.InvNo = #INV.InvNo
ORDER BY #INV.Currency, #INV.InvNo
[/font]
Produces the following results set: Note that invoices having multiple payments are listed more than once.
So sum(Amount) includes the repeated invoices multiple times, yielding the wrong total. This was the "obtained" column.
[font="Courier New"]
InvNoCurrencyGrandTotalPaid
4 EUR 2001.00 200.20
4 EUR 2001.00 200.20
4 EUR 2001.00 200.20
4 EUR 2001.00 200.20
5 EUR 2011.00 NULL
6 EUR 2111.00 NULL
7 EUR 3001.00 NULL
9 EUR 3111.00 NULL
10 EUR 3111.00 NULL
1 USD 1001.00 333.6667
1 USD 1001.00 333.6667
2 USD 1011.00 NULL
3 USD 1111.00 NULL
8 USD 3011.00 NULL
[/font]
Ahh you had me confused. I thought you wanted that in the output. I had put together my queries already when I posted but couldn't for the life of me figure out where those values came from. Now I see you were just posting it to show what you had done. :hehe:
Well you now have at least 3 different ways to achieve your results. Hope at least one of them works for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 30, 2013 at 1:25 pm
Sean Lange:
Yes, both work. Since I inserted you cte solution after my own statements, I had to add a semi-colon before the "with GrandTotal as".
Thanks
May 30, 2013 at 1:39 pm
j-1064772 (5/30/2013)
Sean Lange:Yes, both work. Since I inserted you cte solution after my own statements, I had to add a semi-colon before the "with GrandTotal as".
Thanks
Well actually a CTE requires that the statement BEFORE it is terminated with a semi-colon. Many people will put the semi-colon at the beginning of their cte which will work.
The semi-colon is a line terminator not a line beginifier. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 30, 2013 at 1:42 pm
I was used to ssql2k when I did not need no smelly semi-colons.
Not clear yet as to why this was added to the language.
And yes, you are right about where it goes.:-)
May 30, 2013 at 1:47 pm
j-1064772 (5/30/2013)
I was used to ssql2k when I did not need no smelly semi-colons.Not clear yet as to why this was added to the language.
And yes, you are right about where it goes.:-)
The semi-colons have always been there as terminators. They are only required for some statements. CTEs require the previous statement to be terminated with one. MERGE requires that the statement itself is terminated with one. Eventually it will be required on all statements.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply