Example: Calculating the Median Absolute Deviation in SQL Server for Item level PO pricing and usage

MAD Example.sqlserver.sql

declare @Items as table(
   item         varchar(35) primary key
   ,MedianPrice decimal(20,6)
   ,MADprice    decimal(20,6)
   ,AvgPrice    decimal(20,6)
   ,MedianQty   decimal(20,6)
   ,MADqty      decimal(20,6)
--   ,QtySum      decimal(20,6)
--   ,QtyCount    decimal(20,6)
   ,AvgQty      decimal(20,6)
   ,UsageCt     int
);

insert into @Items(item,MedianPrice,MedianQty)
select distinct
   item
   ,PERCENTILE_CONT(0.5)
      within group (order by lowpoprice)
      over (partition by item) as MedianPrice
   ,PERCENTILE_CONT(0.5)
      within group (order by lowpoqty)
      over (partition by item) as MedianQty
from SupplyChain.dbo.PurchaseOrderLines
   where ItemType in ('Stock on Hand','Just in Time');

declare @POlines as table(
    item       varchar(35)
   ,Price      decimal(20,6)
   ,PriceDelta decimal(20,6)
   ,Qty        decimal(20,6)
   ,QtyDelta   decimal(20,6)
   ,index ItemNdx nonclustered(item)
);

insert into @POlines(item,Price,PriceDelta,Qty,QtyDelta)
select
    po.item
   ,po.LowPOPrice
   ,abs(po.LowPOPrice-ite.MedianPrice)
   ,po.LowPOQty
   ,abs(po.LowPOQty-ite.MedianQty)
from SupplyChain.dbo.PurchaseOrderLines po
inner join @Items ite on
   po.Item = ite.item;

with rhs as (
   select distinct
      item
      ,PERCENTILE_CONT(0.5)
         within group (order by pricedelta)
         over (partition by item) as MADprice
      ,PERCENTILE_CONT(0.5)
         within group (order by qtydelta)
         over (partition by item) as MADqty
   from @POlines
)
update lhs set
   lhs.MADprice = rhs.MADprice
   ,lhs.MADqty = rhs.MADqty
from @Items lhs
inner join rhs on
   lhs.item = rhs.item;

with rhs as (
   select 
      ln.item 
      ,sum(ln.Price) as sm
      ,count(ln.item) as ct
   from @POlines ln 
   inner join @Items ite on 
      ln.item=ite.item
   where ln.PriceDelta <= ite.MADprice
   group by ln.item
)
update lhs set AvgPrice = rhs.sm/rhs.ct
from @Items lhs inner join rhs on lhs.item=rhs.item;

with rhs as (
   select 
      ln.item 
      ,sum(ln.Qty) as sm
      ,count(ln.item) as ct
   from @POlines ln 
   inner join @Items ite on 
      ln.item=ite.item
   where ln.QtyDelta <= ite.MADqty
   group by ln.item
)
update lhs set AvgQty = rhs.sm/rhs.ct
from @Items lhs inner join rhs on lhs.item=rhs.item;

with rhs as (
   select 
      ln.item 
      ,sum(ln.Qty) as sm
      ,count(ln.item) as ct
   from @POlines ln 
   inner join @Items ite on 
      ln.item=ite.item
   where ln.QtyDelta <= ite.MADqty
   group by ln.item
)
update lhs set AvgQty = rhs.sm/rhs.ct
from @Items lhs inner join rhs on lhs.item=rhs.item;

--select item,AvgPrice,AvgQty from @Items where AvgPrice is not null order by item;
delete from @Items where AvgPrice is null or AvgPrice = 0.0 or AvgQty = 0.0;

select 
   po.*
   ,fltr.AvgPrice,fltr.AvgQty
   ,100*abs(fltr.AvgPrice-po.LowPOPrice)/fltr.AvgPrice PricePctVar
   ,100*abs(fltr.AvgQty-po.LowPOQty)/fltr.AvgQty QtyPctVar
from SupplyChain.dbo.PurchaseOrderLines po
inner join @Items fltr on
   po.Item = fltr.item
where abs(fltr.AvgPrice-po.LowPOPrice)/fltr.AvgPrice > 0.5
   or abs(fltr.AvgQty-po.LowPOQty)/fltr.AvgQty > 0.5;