Search code examples
mysqlsqlmultiple-tablesinventory-management

Getting the Total Item Quanity from previous Transactions by Date of Transaction and Getting Current Item Quantity Total

I have 3 Tables Namely: Inventory:

|ID|ItemID|TransactionDate     |Item             |Unit|Quantity|
----------------------------------------------------------------
|1 |1     |2019-07-10 12:23:51 |Plastic Cup 22oz |Pc  |200     |
----------------------------------------------------------------
|2 |2     |2019-07-09 01:23:51 |Plastic Cup 16oz |Pc  |100     |
----------------------------------------------------------------
|3 |1     |2019-07-10 01:23:51 |Plastic Cup 22oz |Pc  |100     |
----------------------------------------------------------------
|4 |3     |2019-07-09 01:23:51 |Lemon            |Pc  |100     |
----------------------------------------------------------------
|5 |2     |2019-07-10 01:23:51 |Plastic Cup 16oz |Pc  |100     |
----------------------------------------------------------------
|6 |1     |2019-07-09 01:23:51 |Plastic Cup 22oz |Pc  |100     |
----------------------------------------------------------------
|7 |3     |2019-07-10 01:23:51 |Lemon            |Pc  |100     |
----------------------------------------------------------------

ConsumedProducts:

|ID|TID|TransactionDate     |ItemID|Item             |Unit|Quantity|
------------------------------------------------------------------
|1 |1  |2019-07-10 12:23:51 |1     |Plastic Cup 22oz |Pc  |1     |
------------------------------------------------------------------
|2 |1  |2019-07-09 01:23:51 |3     |Lemon            |Pc  |1     |
------------------------------------------------------------------
|3 |2  |2019-07-10 01:23:51 |2     |Plastic Cup 16oz |Pc  |1     |
------------------------------------------------------------------
|4 |2  |2019-07-09 01:23:51 |3     |Lemon            |Pc  |1     |
------------------------------------------------------------------
|5 |3  |2019-07-10 01:23:51 |2     |Plastic Cup 16oz |Pc  |1     |
------------------------------------------------------------------

DamagedProducts:

|ID|TransactionDate     |ItemID|Item             |Unit|Quantity|
----------------------------------------------------------------
|1 |2019-07-10 12:23:51 |1     |Plastic Cup 22oz |Pc  |10      |
----------------------------------------------------------------
|2 |2019-07-9 01:23:51 |2     |Plastic Cup 16oz |Pc  |10      |
----------------------------------------------------------------
|3 |2019-07-10 01:23:51 |1     |Plastic Cup 22oz |Pc  |5       |
----------------------------------------------------------------
|4 |2019-07-10 01:23:51 |3     |Lemon            |Pc  |6       |
----------------------------------------------------------------

Target Output: Lets say the current date is (2019-07-10)

  1. Prev Bal= Inventory - (Cunsumed + Damaged) "From the Date Before the Current Date"
  2. Items Delivered Today (Reflected on "Inventory" Table)
  3. Current Consumed (Reflected on "ConsumedProducts" Table)
  4. Current Damaged(Reflected on "DamagedProducts" Table)
  5. Remaining Balance= (Prev Bal + Items Delivered Today) - (Current Consumed+Current Damaged

|ID|ItemID|Item|Prev Bal|Current Delivered|Current Consumed|Current Damaged|Balance| --------------------------------------------------------------------------------------

I had previously asked how to sum Tables on this forum somehow I was able to get good results thanks for the help of this community but I recently had this problem as stated above.

Select I.ID, I.Item,
                    (Select IFNULL(SUM( (SELECT Quantity FROM inventory WHERE DATE(ItemTransactionDate) < CURDATE() GROUP BY I.ItemID)),0) - 
                    IFNULL((SELECT Quantity FROM inventory WHERE DATE(ItemTransactionDate) < CURDATE() GROUP BY I.ItemID),0) - 
                    IFNULL((SELECT Quantity FROM consumeditemmonitoring WHERE DATE(TransactionDate) < CURDATE() GROUP BY I.ItemID),0) As NEWBALANCE From inventory INV 
                    LEFT OUTER JOIN ( Select ItemID,IFNULL(SUM(Quantity),0) As Quantity from damagedinventory group by ItemID) DMG On INV.ItemID=DMG.ItemID 
                    LEFT OUTER JOIN (Select ItemID,IFNULL(SUM(Quantity),0) As Quantity from consumeditemmonitoring group by ItemID) CSMD On INV.ItemID=CSMD.ItemID GROUP BY I.ItemID),

                    (SELECT SUM(Quantity) As CurrentDeliveries FROM inventory WHERE DATE(ItemTransactionDate) = CURDATE() GROUP BY I.ItemID),

                    IFNULL(D.Quantity,0) As damagedQTY,

                    IFNULL(C.Quantity,0) As ConsumedQTY,

                    IFNULL(SUM(I.Quantity),0) - IFNULL(D.Quantity,0) - IFNULL(C.Quantity,0) As NEWBALANCE From inventory I LEFT OUTER JOIN 
                    (Select ItemID,IFNULL(SUM(Quantity),0) As Quantity from damagedinventory group by ItemID) D On I.ItemID=D.ItemID 
                    LEFT OUTER JOIN (Select ItemID,IFNULL(SUM(Quantity),0) As Quantity from consumeditemmonitoring group by ItemID) C On I.ItemID=C.ItemID GROUP BY I.ItemID"

I'm a newbie in programming so I really don't have sufficient knowledge on this. Any help would be very much appreciated thank you.

Solution

  • Hope that this works, or maybe you can adapt it. I used lag function. This shows you the previous value of the column (read also about the lead function here)

    SQL QUERY:

    SELECT I.ID,
       I.ItemID,
       PREV_BAL,
       ITEMS_DELIVERED_TODAY,
       CURRENT_CONSUMED,
       CURRENT_DAMAGED,
       PREV_BAL + ITEMS_DELIVERED_TODAY - CURRENT_CONSUMED - CURRENT_DAMAGED
           AS REMANING_BALANCE
  FROM (SELECT I.ID,
               I.ItemID,
               (  LAG (
                      I.Quantity,
                      1)
                  OVER (PARTITION BY I.ItemID
                        ORDER BY TRUNC (TransactionDate))
                - D.prevQuantity
                - C.prevQuantity)
                   PREV_BAL,
               I.Quantity ITEMS_DELIVERED_TODAY,
               C.Quantity CURRENT_CONSUMED,
               D.Quantity CURRENT_DAMAGED
          FROM inventory  I
               LEFT OUTER JOIN
               (  SELECT ItemID,
                         IFNULL (SUM (Quantity), 0)   AS Quantity,
                         IFNULL (SUM (prevQuantity), 0) AS prevQuantity
                    FROM (SELECT ItemID,
                                 Quantity,
                                 LAG (
                                     Quantity,
                                     1)
                                 OVER (PARTITION BY ItemID
                                       ORDER BY TRUNC (TransactionDate))
                                     prevQuantity
                            FROM damagedinventory) damagedinventory
                GROUP BY ItemID) D
                   ON I.ItemID = D.ItemID
               LEFT OUTER JOIN
               (  SELECT ItemID,
                         IFNULL (SUM (Quantity), 0)   AS Quantity,
                         IFNULL (SUM (prevQuantity), 0) AS prevQuantity
                    FROM (SELECT ItemID,
                                 Quantity,
                                 LAG (
                                     Quantity,
                                     1)
                                 OVER (PARTITION BY ItemID
                                       ORDER BY TRUNC (TransactionDate))
                                     prevQuantity
                            FROM consumeditemmonitoring) consumeditemmonitoring
                GROUP BY ItemID) C
                   ON I.ItemID = C.ItemID)