Hello, guys
I have an example query which includes a complex join with 4 tables (actually 5, because one of them must be aliased twice). I could not find another option but to use #temporary tables to store some of the inner joins. Do any of you have any idea how could this query be rewritten without using the temporary tables?
There are the following tables: Client (Id, Name), OrderType (Id, Name = {'sell', 'buy'}), Order(Id, ClientId, OrderTypeId), OrderItem (OrderId, Quantity, Price)
I need to get the following result: A table with the names of the clients that made a positive turnover:
Client name | Turnover
Where each client's Turnover is calculated as the difference between the sum of all Orders of type 'buy' and the sum of all Orders of type 'sell'
or turnover = (SUM(buy) - SUM(sell))
I am quite puzzled, need to confess I haven't written many complex queries by now. Here is what I came to:
SELECT [Order].ClientId, SUM(OrderItem.Quantity*OrderItem.Price) AS 'Sell'
INTO #Sell
FROM OrderItem
INNER JOIN [Order]
ON [Order].Id = OrderItem.OrderId
WHERE [Order].OrderTypeId=
(SELECT Id FROM OrderType WHERE [Name] = 'sell')
GROUP BY([Order].ClientId)
SELECT [Order].ClientId, SUM(OrderItem.Quantity*OrderItem.Price) AS 'Buy'
INTO #Buy
FROM OrderItem
INNER JOIN [Order]
ON [Order].Id = OrderItem.OrderId
WHERE [Order].OrderTypeId=
(SELECT Id FROM OrderType WHERE [Name] = 'buy')
GROUP BY([Order].ClientId)
SELECT Client.[Name], b.Buy - s.Sell AS 'Turnover'
FROM Client
INNER JOIN
(#Buy AS b
INNER JOIN #Sell AS s
ON b.ClientId = s.ClientId)
ON Client.Id = s.ClientId
WHERE (b.Buy - s.Sell) > 0
It works fine but I was wondering if I could skip the #temp tables.
If you have any ideas I will appreciate.
Thanks
