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>​
-