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

Question

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)

Prev Bal= Inventory - (Cunsumed + Damaged) "From the Date Before the Current Date"
Items Delivered Today (Reflected on "Inventory" Table)
Current Consumed (Reflected on "ConsumedProducts" Table)
Current Damaged(Reflected on "DamagedProducts" Table)
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.

I need to have the output as Stated on the Targeted Output sir. The figure below that is the proposed table — Matt Pedrosa, Jul 19 '19 at 07:28
Can you edit your post with an example of what your query return in this moment? — F.Lazarescu, Jul 19 '19 at 07:31

score 1 · Accepted Answer · answered Jul 19 '19 at 10:23

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)

Sorry for the Iate reply sir because I was away for a while but I get this error.(" You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OVER (PARTITION BY I.ItemID ORDER BY TRUNC (Transaction'") — Matt Pedrosa, Jul 22 '19 at 02:23
I have read the about lag but can't really seem to cope it for now. — Matt Pedrosa, Jul 22 '19 at 02:25
Regarding the lag function: this function lets you to return the value of previous n rows, where n is the second parameter of the function, in our case 1. So lag(quantity,1) over (partition by ItemID order by transactionDate) shows us the previous quantity of ItemID 1 transactiondate 2019-07-10 01:23:51 - example — F.Lazarescu, Jul 22 '19 at 06:18

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

1 Answers1