Northwind Database - Creating trading balance between countries.
-
I have quite a task to do.
Using an old Northwind database, I need to create a trading balance for each country.
Which means, I need to count, how much each country sold to each country, how much they've bought and what's the balance, on plus or minus. So far I have this:
SELECT sc.CountryName as SuppliersCountry,
cc.CountryName as CustomerCountry,
SUM(CONVERT(money, (od.UnitPrice * od.Quantity) * (1 - od.Discount) / 100) * 100) AS Total
From Suppliers s
INNER JOIN Countries sc on s.CountryID = sc.CountryID
INNER JOIN Products p on s.SupplierID = p.SupplierID
INNER JOIN [Order Details] od on p.ProductID = od.ProductID
INNER JOIN Orders o on od.OrderID = o.OrderID
INNER JOIN Customers cu on o.CustomerID = cu.CustomerID
INNER JOIN Countries cc on cu.CountryID = cc.CountryID
GROUP BY sc.CountryName, cc.CountryName
ORDER BY sc.CountryName
Sadly it gives me only one sided trading balance.
What I need to do, is to create a query with such equation "Country A - Country B - (Total of AB - BA)"
How can I modify my query to get such result? - margor20 - Friday, April 27, 2018 4:21 AM
This is a good first step. What I would do is SELECT.. INTO.. a temp table and then do write another SELECT that performs a self join. Perhaps something like this:
SELECT (A.Total - B.Total) ...
...
FROM #Trade as A
JOIN #Trade as B on B.SuppliersCountry = A.CustomerCountry"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
-
I had some time to work on it, now I have this query:
SELECT
MAX(ca.CountryName) as CountryA,
MIN(cb.CountryName) as CountryB,
SUM((case when ca.CountryName < cb.CountryName then -1 else 1 end) * CONVERT(money, (od.UnitPrice * od.Quantity) * (1 - od.Discount) / 100) * 100) as Balance
FROM Orders o
INNER JOIN [Order Details] od on o.OrderID=od.OrderID
INNER JOIN Products p on p.ProductID = od.ProductID
INNER JOIN Suppliers s on s.SupplierID = p.SupplierID
INNER JOIN Customers c on o.CustomerID = c.CustomerID
INNER JOIN Countries ca ON ca.CountryID = s.CountryID
INNER JOIN Countries cb ON cb.CountryID = c.CountryID
GROUP BY (case when ca.CountryName < cb.CountryName then ca.CountryName+' to '+cb.CountryName else cb.CountryName+' to '+ca.CountryName end)
ORDER BY CountryA, CountryB
It looks pretty much as what I want to have, but I get duplicates, like:Italy Italy 2100How can I skip them?
---------------EDIT-------------
WHERE NOT cb.CountryName = ca.CountryName
Duh ;p - margor20 - Friday, April 27, 2018 7:05 AM
There is a HAVING clause that can be used in conjunction with GROUP BY clause.
GROUP BY ...
HAVING cb.CountryName = ca.CountryName
ORDER BY ..."Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
-
Hello again!
Sadly my problem still exists. This query right here:
SELECT
ca.CountryName,
cb.CountryName,
SUM((case when ca.CountryName < cb.CountryName then -1 else 1 end) * CONVERT(money, (od.UnitPrice * od.Quantity) * (1 - od.Discount) / 100) * 100) as Balance
FROM Orders o
INNER JOIN [Order Details] od on o.OrderID=od.OrderID
INNER JOIN Products p on p.ProductID = od.ProductID
INNER JOIN Suppliers s on s.SupplierID = p.SupplierID
INNER JOIN Customers c on o.CustomerID = c.CustomerID
INNER JOIN Countries ca ON ca.CountryID = s.CountryID
INNER JOIN Countries cb ON cb.CountryID = c.CountryID
WHERE NOT cb.CountryName = ca.CountryName
GROUP BY ca.CountryName, cb.CountryName
ORDER BY ca.CountryName, cb.CountryName
Is still not good. I've realised that it always shows negative results for countries under CountryA in alphabetical order. It gives something like that:
What I need is to find a correct SUM expression, that will help me to count the difference in tradings between countries.
My database has a structure like this:
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply