Explanation of SQL

This SQL will create a function that returns a table with any discrepencies between the inventory counts of a part and what the inventory should be based on a recreation of all events in the inventory log table.

Risk of Data Corruption if Run Improperly

None. This is a selection query and no data is modified in the running of it.

SQL

SQL to Create Function

code_formatsql|code format"sql"

–Transact-SQL Multistatement Table-valued Function Syntax

CREATE FUNCTION Check_CompareInventoryToLog()

RETURNS @result

TABLE (

  RowID INT Primary Key,
  InventoryID INT,
  PartID INT,
  DivisionID INT,
  WarehouseID INT,
  PartName varchar(155),
  BilledInv FLOAT,
  ROInv FLOAT,
  OnHandInv FLOAT,
  ReservedInv FLOAT,
  AvailableInv FLOAT,
  OnOrderInv FLOAT,
  ExpectedInv FLOAT,
  BilledLog FLOAT,
  ROLog FLOAT,
  OnHandLog FLOAT,
  ReservedLog FLOAT,
  AvailableLog FLOAT,
  OnOrderLog FLOAT,
  ExpectedLog FLOAT,
  BilledDelta FLOAT,
  RODelta FLOAT,
  OnHandDelta FLOAT,
  ReservedDelta FLOAT,
  AvailableDelta FLOAT,
  OnOrderDelta FLOAT,
  ExpectedDelta FLOAT
  )

AS

BEGIN

  DECLARE @UnbalancedParts Table(ID INT Primary Key);
  Insert into @UnbalancedParts
  Select Coalesce( Inv.ID, IL.InventoryID, -1 )
  from
  ( select ID, PartID, Sum(QuantityBilled) QuantityBilled, SUM(QuantityReceivedOnly) QuantityReceivedOnly, SUM(QuantityOnHand) QuantityOnHand, SUM(QuantityReserved) QuantityReserved, SUM(QuantityAvailable) QuantityAvailable, SUM(QuantityOnOrder) QuantityOnOrder, SUM(QuantityExpected) QuantityExpected
    from Inventory
    Where ID > 0 and ClassTypeID  12200 and PartID is not NULL
    Group By ID, PartID ) Inv
  Full Join
  ( select InventoryID, PartID, Sum(QuantityBilled) QuantityBilled, SUM(QuantityReceivedOnly) QuantityReceivedOnly, SUM(QuantityOnHand) QuantityOnHand, SUM(QuantityReserved) QuantityReserved, SUM(QuantityAvailable) QuantityAvailable, SUM(QuantityOnOrder) QuantityOnOrder, SUM(QuantityExpected) QuantityExpected
    from InventoryLog
    Where ID > 0 and InventoryID is not NULL
    group By InventoryID, PartID ) IL on (Inv.ID  IL.InventoryID)
  Where ( ABS( Coalesce(IL.QuantityBilled, 0) - Coalesce(Inv.QuantityBilled, 0) ) > 0.00001 )
        OR ( ABS( Coalesce(IL.QuantityReceivedOnly, 0) - Coalesce(Inv.QuantityReceivedOnly, 0) ) > 0.00001 )
        OR ( ABS( Coalesce(IL.QuantityReserved, 0) - Coalesce(Inv.QuantityReserved, 0) ) > 0.00001 )
        OR ( ABS( Coalesce(IL.QuantityOnOrder, 0) - Coalesce(Inv.QuantityOnOrder, 0) ) > 0.00001 )
  ;
  DECLARE @InventoryTotal Table(InventoryID INT Primary Key,
                      DivisionID INT, WarehouseID INT,
                      PartID INT, EntryType INT,
                      QuantityBilled FLOAT, QuantityReceivedOnly FLOAT, QuantityOnHand FLOAT,
                      QuantityReserved FLOAT, QuantityAvailable FLOAT, QuantityOnOrder FLOAT, QuantityExpected FLOAT)
  ;
  -- 1  Inventory
  insert into @InventoryTotal
  select ID as InventoryID,
         coalesce(DivisionID, 10) as DivisionID,
         coalesce(WarehouseID, 10) as WarehouseID,
         PartID, 1 as EntryType,
         Sum(QuantityBilled) QuantityBilled, SUM(QuantityReceivedOnly) QuantityReceivedOnly,
         SUM(QuantityOnHand) QuantityOnHand, SUM(QuantityReserved) QuantityReserved,
         SUM(QuantityAvailable) QuantityAvailable, SUM(QuantityOnOrder) QuantityOnOrder,
         SUM(QuantityExpected) QuantityExpected
  from Inventory
  Where ID in (Select ID from @UnbalancedParts)
        and ClassTypeID  12200
  Group By ID, PartID, coalesce(DivisionID, 10), coalesce(WarehouseID, 10)
  ;
  DECLARE @LogTotal Table(InventoryID INT Primary Key,
                      DivisionID INT, WarehouseID INT,
                      PartID INT, EntryType INT,
                      QuantityBilled FLOAT, QuantityReceivedOnly FLOAT, QuantityOnHand FLOAT,
                      QuantityReserved FLOAT, QuantityAvailable FLOAT, QuantityOnOrder FLOAT, QuantityExpected FLOAT)
  ;
  -- 2  Full Log
  insert into @LogTotal
  select InventoryID,
         null as DivisionID, null as WarehouseID, -- not needed, because tied to warehouse
         PartID, 2 as EntryType,
         Sum(QuantityBilled) QuantityBilled, SUM(QuantityReceivedOnly) QuantityReceivedOnly,
         SUM(QuantityOnHand) QuantityOnHand, SUM(QuantityReserved) QuantityReserved,
         SUM(QuantityAvailable) QuantityAvailable, SUM(QuantityOnOrder) QuantityOnOrder,
         SUM(QuantityExpected) QuantityExpected
  from InventoryLog
  Where ID > 0 and InventoryID in (Select ID from @UnbalancedParts) and InventoryID is not NULL
  group By InventoryID, PartID
  ;
  insert into @Result
  select
      ROW_NUMBER() OVER(Order By InventoryID) as RowID,
      InventoryID,
      PartID,
      DivisionID,
      WarehouseID,
      PartName,
      BilledInv,
      ROInv,
      OnHandInv,
      ReservedInv,
      AvailableInv,
      OnOrderInv,
      ExpectedInv,
      BilledLog,
      ROLog,
      OnHandLog,
      ReservedLog,
      AvailableLog,
      OnOrderLog,
      ExpectedLog,
      BilledDelta,
      RODelta,
      BilledDelta + RODelta as OnHandDelta,
      ReservedDelta,
      BilledDelta + RODelta - ReservedDelta as AvailableDelta,
      OnOrderDelta,
      BilledDelta + RODelta - ReservedDelta + OnOrderDelta as ExpectedDelta
  from
  (
      select Coalesce( I.InventoryID, L.InventoryID ) InventoryID,
             Coalesce( I.PartID, L.PartID ) PartID,
             (Select ItemName from Part where ID  Coalesce( I.PartID, L.PartID )) PartName,
             I.DivisionID as DivisionID,
             I.WarehouseID as WarehouseID,
             Round( I.QuantityBilled - L.QuantityBilled, 6) as BilledDelta,
             Round( I.QuantityReceivedOnly - L.QuantityReceivedOnly, 6) as RODelta,
             Round( I.QuantityReserved - L.QuantityReserved, 6) as ReservedDelta,
             Round(I.QuantityOnOrder - L.QuantityOnOrder, 6) as OnOrderDelta,
             I.QuantityBilled as BilledInv,
             L.QuantityBilled as BilledLog,
             I.QuantityReceivedOnly as ROInv,
             L.QuantityReceivedOnly as ROLog,
             I.QuantityOnHand as OnHandInv,
             L.QuantityOnHand as OnHandLog,
             I.QuantityReserved as ReservedInv,
             L.QuantityReserved as ReservedLog,
             I.QuantityAvailable as AvailableInv,
             L.QuantityAvailable as AvailableLog,
             I.QuantityOnOrder as OnOrderInv,
             L.QuantityOnOrder as OnOrderLog,
             I.QuantityExpected as ExpectedInv,
             L.QuantityExpected as ExpectedLog
      from @InventoryTotal I
           Full Join @LogTotal L on (L.InventoryID  I.InventoryID)
  ) Detail
  where (BilledDelta  0) or (RODelta  0) or (ReservedDelta  0) or (OnOrderDelta  0)
  Order by PartName, BilledDelta, RODelta, ReservedDelta, OnOrderDelta
  RETURN

END

;

<code> SQL to Use Function to Return Discrepencies code_formatsql|code format"sql"

select *

from Check_CompareInventoryToLog()

<code> Sample Results The following shows a sample result set returned from the query. (This table has been transposed to show all the information.)

RowID 1 2
InventoryID 1007 1008
PartID 1003 151
DivisionID 10 10
WarehouseID 10 10
PartName Hasp - USB Other Labor
BilledInv 0 -2019.32
ROInv 0 0
OnHandInv 0 -2019.32
ReservedInv 69 207.82
AvailableInv -69 -2227.14
OnOrderInv 0 0
ExpectedInv -69 -2227.14
BilledLog 0 -25643.03
ROLog 0 0
OnHandLog 0 -25643.03
ReservedLog 59 207.82
AvailableLog -59 -25850.85
OnOrderLog 0 0
ExpectedLog -59 -25850.85
BilledDelta 0 23623.71
RODelta 0 0
OnHandDelta 0 23623.71
ReservedDelta 10 0
AvailableDelta -10 23623.71
OnOrderDelta 0 0
ExpectedDelta -10 23623.71

Version Information

  • Entered : 01/2011
  • Version : 4.5+

Related SQLs

You could leave a comment if you were logged in.