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