Skip to content

Instantly share code, notes, and snippets.

@Kagre
Created December 8, 2021 20:21
Show Gist options
  • Save Kagre/a72e981f405927a861e52ee96f11ca26 to your computer and use it in GitHub Desktop.
Save Kagre/a72e981f405927a861e52ee96f11ca26 to your computer and use it in GitHub Desktop.
Example: Calculating the Median Absolute Deviation in SQL Server for Item level PO pricing and usage
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment