Differences

This shows you the differences between two versions of the page.

Link to this comparison view

control_sql_-_check_all_inventory_levels_v_all_inventory_logs [2019/01/27 11:28]
127.0.0.1 external edit 		control_sql_-_check_all_inventory_levels_v_all_inventory_logs [2019/02/01 10:08] (current)
shendrix [SQL #1 - Identify Discrepencies]
Line 18: Line 18:
  
 ====== SQL #1 - Identify Discrepencies ====== ====== SQL #1 - Identify Discrepencies ======
- 
- 
  
 <code sql> <code sql>
 SELECT * FROM( SELECT * FROM(
 SELECT SELECT
-INV.ID as InventoryID +INV.ID AS InventoryID 
-, INV.PartID as PartID+, INV.PartID AS PartID
 , P.ItemName , P.ItemName
-, SUM(INVL.QuantityBilled) as QuantityBilled +, SUM(INVL.QuantityBilled) AS QuantityBilled 
-, SUM(INVL.QuantityReceivedOnly) as QuantityReceivedOnly +, SUM(INVL.QuantityReceivedOnly) AS QuantityReceivedOnly 
-, SUM(INVL.QuantityOnHand) as QuantityOnHand +, SUM(INVL.QuantityOnHand) AS QuantityOnHand 
-, SUM(INVL.QuantityReserved) as QuantityReserved +, SUM(INVL.QuantityReserved) AS QuantityReserved 
-, SUM(INVL.QuantityAvailable) as QuantityAvailable +, SUM(INVL.QuantityAvailable) AS QuantityAvailable 
-, SUM(INVL.QuantityOnOrder) as QuantityOnOrder +, SUM(INVL.QuantityOnOrder) AS QuantityOnOrder 
-, SUM(INVL.QuantityExpected) as QuantityExpected +, SUM(INVL.QuantityExpected) AS QuantityExpected 
-, INV.QuantityBilled as INV_QuantityBilled +, INV.QuantityBilled AS INV_QuantityBilled 
-, INV.QuantityReceivedOnly as INV_QuantityReceivedOnly +, INV.QuantityReceivedOnly AS INV_QuantityReceivedOnly 
-, INV.QuantityOnHand as INV_QuantityOnHand +, INV.QuantityOnHand AS INV_QuantityOnHand 
-, INV.QuantityReserved as INV_QuantityReserved +, INV.QuantityReserved AS INV_QuantityReserved 
-, INV.QuantityAvailable as INV_QuantityAvailable +, INV.QuantityAvailable AS INV_QuantityAvailable 
-, INV.QuantityOnOrder as INV_QuantityOnOrder +, INV.QuantityOnOrder AS INV_QuantityOnOrder 
-, INV.QuantityExpected as INV_QuantityExpected+, INV.QuantityExpected AS INV_QuantityExpected
 FROM InventoryLog INVL FROM InventoryLog INVL
 LEFT JOIN Inventory INV ON INV.ID = INVL.InventoryID LEFT JOIN Inventory INV ON INV.ID = INVL.InventoryID
 LEFT JOIN Part P ON P.ID = INV.PartID LEFT JOIN Part P ON P.ID = INV.PartID
-WHERE +WHERE
 INV.ID IS NOT NULL INV.ID IS NOT NULL
-GROUP BY +GROUP BY
 P.ItemName P.ItemName
 , INV.PartID , INV.PartID
Line 58: Line 56:
 , INV.ID , INV.ID
 ) Tmp ) Tmp
-WHERE  +WHERE 
-(ROUND(QuantityBilled,2)  ROUND(INV_QuantityBilled,2)) +(ROUND(QuantityBilled,2) != ROUND(INV_QuantityBilled,2)) 
-OR (ROUND(QuantityReceivedOnly,2)  ROUND(INV_QuantityReceivedOnly,2)) +OR (ROUND(QuantityReceivedOnly,2) != ROUND(INV_QuantityReceivedOnly,2)) 
-OR (ROUND(QuantityOnHand,2)  ROUND(INV_QuantityOnHand,2)) +OR (ROUND(QuantityOnHand,2) != ROUND(INV_QuantityOnHand,2)) 
-OR (ROUND(QuantityReserved,2)  ROUND(INV_QuantityReserved,2)) +OR (ROUND(QuantityReserved,2) != ROUND(INV_QuantityReserved,2)) 
-OR (ROUND(QuantityAvailable,2)  ROUND(INV_QuantityAvailable,2)) +OR (ROUND(QuantityAvailable,2) != ROUND(INV_QuantityAvailable,2)) 
-OR (ROUND(QuantityOnOrder,2)  ROUND(INV_QuantityOnOrder,2)) +OR (ROUND(QuantityOnOrder,2) != ROUND(INV_QuantityOnOrder,2)) 
-OR (ROUND(QuantityExpected,2)  ROUND(INV_QuantityExpected,2))+OR (ROUND(QuantityExpected,2) != ROUND(INV_QuantityExpected,2))
 </code> </code>
-