Differences

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

Link to this comparison view

control_sql_-_add_function_to_compare_inventory_totals_to_log_records [2019/01/27 11:28] (current)
Line 1: Line 1:
 +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_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_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