-1

I have 2 tables; Inbound & Outbound. Both of them have the same structure.

  • TransactionId (int),
  • Date (datetime),
  • StoreItemId (int) [FK],
  • Quantity (real)

I tried this select statement and the result as in the image below:

SELECT [StoreItemId], SUM([Quantity]) AS 'inbound' FROM [Inbound] GROUP BY [StoreItemId];
SELECT [StoreItemId], SUM([Quantity]) AS 'outbound' FROM [Outbound] GROUP BY [StoreItemId];

query and result

I tried a query:

SELECT
    (SELECT SUM([Quantity]) AS 'inbound' FROM [Inbound] WHERE [Inbound].[StoreItemId] = 1) - 
    (SELECT SUM([Quantity]) AS 'Outbound' FROM [Outbound] WHERE [Outbound].[StoreItemId] = 1)

and the result was 105 (115-10 ; Inbound quantity for StoreItemId = 1 - Outbound quantity for StoreItemId = 1)

I tried combining both queries above to a query like this:

SELECT [StoreItemId] AS 'xyz'
       (SELECT SUM([Quantity]) FROM [Inbound] WHERE [Inbound].[StoreItemId] = [xyz]) - 
       (SELECT SUM([Quantity]) FROM [Outbound] WHERE [Outbound].[StoreItemId] = [xyz])
FROM [StoreItem]

But it's just wrong

The result should be:

StoreItemId | Balance
----------------------
1           |   105
2           |   126
3           |   78
4           |   144
5           |   100
6           |   179
Taher
  • 565
  • 1
  • 14
  • 32
  • Possible duplicate of [formula for computed column based on different table's column](https://stackoverflow.com/questions/2769007/formula-for-computed-column-based-on-different-tables-column) – Tanner Sep 11 '18 at 12:38
  • I made this statement so far `SELECT [StoreItemId], SUM([Quantity]) AS 'inbound' FROM [Inbound] GROUP BY [StoreItemId];` the same for outbound but I don't know how to subtract the 2 quantities from each other – Taher Sep 11 '18 at 12:42
  • please check the duplicate link i posted. – Tanner Sep 11 '18 at 12:45
  • you might want to re-consider this. When the number of transaction growth, there might be performance issue – Squirrel Sep 11 '18 at 13:07
  • @Squirrel well that changes things, I think I will have to remove the balance table and calculate the balance at the client side inside the Application codes – Taher Sep 11 '18 at 13:14
  • @Tanner I checked the link you mentioned but I guess this is no duplicate as I didn't find what I am looking for – Taher Sep 11 '18 at 13:57
  • Having the balance is fine. but calculating it on the fly might be an issue when the number of transaction increases. What you want with computed column is basically calculate the balance as and when you query that column – Squirrel Sep 12 '18 at 00:54
  • @Squirrel I guess you are right, a query must be made for both Inbound and Outbound tables once a transaction is made. – Taher Sep 12 '18 at 21:17

1 Answers1

0

You can use LEFT JOIN With GROUP BY AND SUM of QUANTITY. You can Refer below Query For same.

select StoreItemId  , (sum(ISNULL(inb.Quantity,0)) - sum(ISNULL(ob.Qunatity,0))) as Balance
from Inbound inb 
LEFT JOIN Outbound ob on ob.StoreItemId = si.StoreItemId
GROUP BY StoreItemId
Bhargav J Patel
  • 166
  • 1
  • 6
  • still returning wrong quantities, it is returning: (1: 305) / (2: 504) / (3: 78) / (4: 138) / (5: 100) / (6: 178) – Taher Sep 12 '18 at 21:19