Search code examples
mysqlsummultiple-tables

Complex Multiple Table Inventory SQL Query

I have 3 Tables: Assuming "2019-07-19" is the (Current Date)

1.Inventory: 

___________________________________________________
|ID| TransactionDate | ItemID | ItemName |Quantity|
+-------------------------------------------------+
|1 |2019-07-18       | 1      |Lemon     |100     |
+-------------------------------------------------+
|2 |2019-07-19       | 2      |Sugar     |100     | 
+-------------------------------------------------+
|3 |2019-07-19       | 2      |Sugar     |100     |
+-------------------------------------------------+
|4 |2019-07-18       | 1      |Lemon     |100     |
+-------------------------------------------------+ 
|5 |2019-07-19       | 1      |Lemon     |100     |
+-------------------------------------------------+
|6 |2019-07-18       | 2      |Sugar     |100     |
+-------------------------------------------------+ 
|7 |2019-07-18       | 1      |Lemon     |100     |
+-------------------------------------------------+

2.ConsumedItems: 

___________________________________________________ 
|ID| TransactionDate | ItemID | ItemName |Quantity|
+-------------------------------------------------+
|1 |2019-07-18       | 1      |Lemon     |10      |
+-------------------------------------------------+
|2 |2019-07-19       | 2      |Sugar     |10      |
+-------------------------------------------------+
|3 |2019-07-19       | 2      |Sugar     |10      |
+-------------------------------------------------+
|4 |2019-07-18       | 1      |Lemon     |10      |
+-------------------------------------------------+
|5 |2019-07-19       | 1      |Lemon     |10      |
+-------------------------------------------------+
|6 |2019-07-18       | 2      |Sugar     |10      |
+-------------------------------------------------+
|7 |2019-07-18       | 1      |Lemon     |10      |
+-------------------------------------------------+

3.DamagedItems:

___________________________________________________  
|ID| TransactionDate | ItemID | ItemName |Quantity|
+-------------------------------------------------+
|1 |2019-07-18       | 1      |Lemon     |10      |
+-------------------------------------------------+
|2 |2019-07-19       | 2      |Sugar     |10      |
+-------------------------------------------------+
|3 |2019-07-19       | 2      |Sugar     |10      |
+-------------------------------------------------+
|4 |2019-07-18       | 1      |Lemon     |10      |
+-------------------------------------------------+  
|5 |2019-07-19       | 1      |Lemon     |10      |
+-------------------------------------------------+
|6 |2019-07-18       | 2      |Sugar     |10      |
+-------------------------------------------------+
|7 |2019-07-18       | 1      |Lemon     |10      |
+-------------------------------------------------+

How can I get this Output?

  1. PrevBalance=[Sum Of All Previous Inventories] - ([Sum of all Previous Damaged] + [Sum Of All Consumed])
  2. DmgToday=[Sum of all Current Damaged]
  3. CnsmdToday=[Sum of all Current Consumed]
  4. DlvrdToday=[Sum of all Items in Inventory Today]
| ItemID | ItemName |PrevBalance|DlvrdToday|DmgToday|CnsmdToday|CurrentBal
+------------------------------------------------------------------------+
|1       |Lemon     | 240       |100       |10      |10        |320      |
+------------------------------------------------------------------------+
|2       |Sugar     | 80        |200       |20      |20        |240      |
+-------------------------------------------------------------------------+

Working Code: 

select Inventory.ItemID, Inventory.ItemName,  
sum(case when Inventory.TransactionDate < CURDATE() then Inventory.Quantity 
else 0 end)-sum(case when ConsumedItems.TransactionDate <CURDATE() then 
ConsumedItems.Quantity else 0 end)-sum(case when 
DamagedItems.TransactionDate<CURDATE() then DamagedItems.Quantity else 0 end) as 'PrevBalance',

sum(case when Inventory.TransactionDate=CURDATE() then Inventory.Quantity else 0 end) as 'DeliveredToday',

sum(case when DamagedItems.TransactionDate=CURDATE() then DamagedItems.Quantity else 0 end) as 'DamagedToday',

sum(case when ConsumedItems.TransactionDate=CURDATE() then ConsumedItems.Quantity else 0 end) as 'ConsumedToday',

sum(case when Inventory.TransactionDate < CURDATE() then Inventory.Quantity else 0 end)-
sum(case when ConsumedItems.TransactionDate <CURDATE() then ConsumedItems.Quantity else 0 end)-
sum(case when DamagedItems.TransactionDate<CURDATE() then DamagedItems.Quantity else 0 end)-
sum(case when ConsumedItems.TransactionDate=CURDATE() then ConsumedItems.Quantity else 0 end)-
sum(case when DamagedItems.TransactionDate=CURDATE() then DamagedItems.Quantity else 0 end)+
sum(case when Inventory.TransactionDate=CURDATE() then Inventory.Quantity else 0 end) as 'Total Balance' 

from Inventory 
join ConsumedItems on ConsumedItems.ID = Inventory.ID 
join DamagedItems on DamagedItems.ID = Inventory.ID
group by  Inventory.ItemID, Inventory.ItemName`
Solution

  • Use the below query

    select Inventory.ItemID, Inventory.ItemName, 
sum(case when Inventory.TransactionDate<currentdate() then Inventory.Quantity else 0 end)+sum(case when ConsumedItems.TransactionDate<currentdate() then ConsumedItems.Quantity else 0 end)-sum(case when DamagedItems.TransactionDate<currentdate() then DamagedItems.Quantity else 0 end) as 'PrevBalance',
sum(case when DamagedItems.TransactionDate=currentdate() then DamagedItems.Quantity else 0 end) as 'DamagedToday',
sum(case when ConsumedItems.TransactionDate=currentdate() then ConsumedItems.Quantity else 0 end) as 'ConsumedToday', 
sum(case when ConsumedItems.TransactionDate=currentdate() then ConsumedItems.Quantity else 0 end)+sum(case when DamagedItems.TransactionDate=currentdate() then DamagedItems.Quantity else 0 end)+sum(case when Inventory.TransactionDate=currentdate() then Inventory.Quantity else 0 end) as 'DeliveredToday' 
from Inventory 
join ConsumedItems on ConsumedItems.ID = Inventory.ID 
join DamagedItems on DamagedItems.ID = Inventory.ID
group by  Inventory.ItemID, Inventory.ItemName