August 7, 2014 at 2:22 pm
Hello All,
So I have been struggling with a way to perform inventory turn calculations in SQL. I'll start off with the formula. Basically the formula is Cost of Goods Sold over last 12 months / Average Inventory Value over the last 12 months.
Short hand as COGS/AvgInventoryValue = Turns
In order to obtain the COGS value I take the Avg(UnitCost) * Sum(InvoicedQty) in the last 12 Months for Sales documents. This is represented as DOCUMENTTYPE = 2
Then divide AvgInventoryValue
To obtain the AvgInventoryValue I need to know the Quantity purchased in the 12 month period * Avg(UnitCost) + QuantityOnHand at the start of the period * Avg(UnitCost)
The purchased documents are represented with DOCUMENTTYPE = 6
The purchases will be positive since we are putting value in. The sales are negative since value is going out.
I'll start with a simple example where the product is at zero Quantity On Hand at the beginning of the period.
--CREAT THE TEMP TABLE
create table #VALUEENTRY
(
ITEMNO VARCHAR (50) NOT NULL
, POSTINGDATEDATETIME NOT NULL
, DOCUMENTTYPEINT NOT NULL
, LOCATIONCODEVARCHAR (50) NOT NULL
, INVOICEDQTYINT NOT NULL
, UNITCOSTDecimal(38,10) NOT NULL
, COSTDecimal(38,10) NOT NULL
, DROPSHIP INT DEFAULT '0'
)
/*
--IF NECESSARY DROP THE TABLE
drop table #VALUEENTRY
*/
--INSERT TEST DATA
INSERT INTO #VALUEENTRY
(
ITEMNO, POSTINGDATE, DOCUMENTTYPE, LOCATIONCODE, INVOICEDQTY, UNITCOST, COST
)
VALUES
('MP120 SIEM1','2014-03-17','2','NJ','-15','2.09','-31.35')
,('MP120 SIEM1','2014-03-12','2','NJ','-20','2.09','-41.80')
,('MP120 SIEM1','2014-02-28','2','NJ','-1200','2.09','-2508')
,('MP120 SIEM1','2014-02-28','2','NJ','-96','2.09','-200.64')
,('MP120 SIEM1','2014-02-20','2','NJ','-300','2.09','-627')
,('MP120 SIEM1','2014-02-11','2','NJ','-600','2.09','-1254')
,('MP120 SIEM1','2014-02-10','2','NJ','-192','2.09','-401.28')
,('MP120 SIEM1','2014-01-24','2','NJ','-144','2.09','-300.96')
,('MP120 SIEM1','2014-01-15','2','NJ','-720','2.09','-1504.80')
,('MP120 SIEM1','2014-01-15','2','NJ','-480','2.09','-1003.20')
,('MP120 SIEM1','2014-01-15','2','NJ','-360','2.09','-752.40')
,('MP120 SIEM1','2014-01-09','2','NJ','-96','2.09','-200.64')
,('MP120 SIEM1','2013-12-27','2','NJ','-200','2.09','-418')
,('MP120 SIEM1','2013-12-13','2','NJ','-5052','2.09','-10558.68')
,('MP120 SIEM1','2013-12-12','2','NJ','-2000','2.09','-4180')
,('MP120 SIEM1','2013-12-06','2','NJ','-960','2.09','-2006.40')
,('MP120 SIEM1','2013-11-18','2','NJ','-960','2.09','-2006.40')
,('MP120 SIEM1','2013-11-15','2','NJ','-480','2.09','-1003.20')
,('MP120 SIEM1','2013-11-06','2','NJ','-24','2.09','-50.16')
,('MP120 SIEM1','2013-10-04','2','NJ','-60','2.09','-125.40')
,('MP120 SIEM1','2013-09-24','2','NJ','-3271','2.09','-6836.39')
,('MP120 SIEM1','2013-09-13','2','NJ','-48','2.09','-100.32')
,('MP120 SIEM1','2013-09-09','2','NJ','-144','2.09','-300.96')
,('MP120 SIEM1','2013-08-29','2','NJ','-288','2.09','-601.92')
,('MP120 SIEM1','2013-08-28','2','NJ','-288','2.09','-601.92')
,('MP120 SIEM1','2013-08-27','6','NJ','18000','2.09','37620')
,('MP120 SIEM1','2013-03-11','2','FL','-24','1.96','-47.04')
,('MP120 SIEM1','2013-03-07','6','FL','24','1.96','47.04')
,('MP120 SIEM1','2013-02-28','2','FL','-492','1.96','-964.32')
,('MP120 SIEM1','2013-02-07','6','FL','268','1.96','525.28')
,('MP120 SIEM1','2013-02-07','6','FL','17376','1.96','34056.96')
,('MP120 SIEM1','2013-02-05','2','FL','-268','1.96','-525.28')
,('MP120 SIEM1','2013-01-25','2','FL','-17376','-14.76','256469.76')
,('MP120 SIEM1','2013-01-25','2','FL','-17376','16.72','-290526.72')
,('MP120 SIEM1','2013-01-16','2','FL','-216','1.96','-423.36')
,('MP120 SIEM1','2013-01-16','2','FL','-240','1.96','-470.40')
,('MP120 SIEM1','2013-01-16','2','FL','0','1.96','-1881.60')
,('MP120 SIEM1','2013-01-16','2','FL','0','-14.76','70848')
,('MP120 SIEM1','2013-01-16','2','FL','-960','0','0')
,('MP120 SIEM1','2013-01-16','2','FL','-240','0','0')
,('MP120 SIEM1','2013-01-16','2','FL','-960','0','0')
,('MP120 SIEM1','2013-01-16','2','FL','-4800','16.72','-80256')
,('MP120 SIEM1','2013-01-15','2','FL','-1440','-14.76','21254.40')
,('MP120 SIEM1','2013-01-15','2','FL','-1440','16.72','-24076.80')
,('MP120 SIEM1','2013-01-15','6','FL','9108','1.96','17851')
,('MP120 SIEM1','2013-01-11','2','FL','-960','1.96','-1881.60')
,('MP120 SIEM1','2013-01-11','2','FL','-960','0','0')
--SELECT ALL TO SEE DATA
SELECT * FROM #VALUEENTRY
--FIND TURNS IN A SIMPLE SCENARIO
select ITEMNO, LOCATIONCODE
, AVG(UNITCOST) as AvgUnitCost
, Sum(INVOICEDQTY)
- ABS((Select Sum(INVOICEDQTY)
from #VALUEENTRY
where DOCUMENTTYPE IN(2) and POSTINGDATE between DATEADD(year,-1,GETDATE()) and DATEADD(year,0,GETDATE()) and DROPSHIP <> 1)) as QtyOnHandInPeriod
, ABS((Select Sum(INVOICEDQTY)
from #VALUEENTRY
where DOCUMENTTYPE IN(2) and POSTINGDATE between DATEADD(year,-1,GETDATE()) and DATEADD(year,0,GETDATE()) and DROPSHIP <> 1)) as QtySoldInPeriod
, AVG(UNITCOST)
* ABS((Select Sum(INVOICEDQTY)
from #VALUEENTRY
where DOCUMENTTYPE IN(2) and POSTINGDATE between DATEADD(year,-1,GETDATE()) and DATEADD(year,0,GETDATE()) and DROPSHIP <> 1)) as COGS
, Sum(INVOICEDQTY)
* AVG(UNITCOST)
/ (AVG(UNITCOST)
* ABS((Select Sum(INVOICEDQTY)
from #VALUEENTRY
where DOCUMENTTYPE IN(2) and POSTINGDATE between DATEADD(year,-1,GETDATE()) and DATEADD(year,0,GETDATE()) and DROPSHIP <> 1))) as Turns
from #VALUEENTRY
where DOCUMENTTYPE IN(6) and POSTINGDATE between DATEADD(year,-1,GETDATE()) and DATEADD(year,0,GETDATE()) and DROPSHIP <> 1
GROUP BY ITEMNO, LOCATIONCODE
/*
BASED ON THESE RESULTS THE NUMBER OF INVENTORY TURNS FOR THE ITEM WAS 1.000111
THIS SOUNDS CORRECT
WHEN I RUN THIS AGAINST THE ACTUAL TABLE I GET INNACCURATE RESULTS AND WHEN
I LOOK AT THE SAME ITEM FROM THIS EXAMPLE THE TURNS RESULT IN 0.001445 WHICH MEANS
IM NOT RUNNING THE QUERY IN A WAY THAT WILL GIVE ACCURATE CALCULATIONS
PLEASE HELP
*/
Is there another approach I should take to obtain the inventory turns?
Also how do I account for periods earlier than 12 months where inventory is left over?
In the sample data this item happened to be at zero inventory on hand at the start of the period.
Also I believe it is my subquery's which are throwing the numbers off but I'm not sure how to isolate the subquery to just perform the result for a specific item. It is aggregating the entire table. How can I make the subquery more specific?
Any help would be appreciated.
August 7, 2014 at 3:31 pm
Hi
You subqueries are returning the sum for all ITEMNOs and LOCATIONCODEs. I suspect you should be joining these to the outer query of ITEMNO and LOCATIONCODE.
select ITEMNO, LOCATIONCODE
, AVG(UNITCOST) as AvgUnitCost
, Sum(INVOICEDQTY)
- ABS((Select Sum(INVOICEDQTY)
from #VALUEENTRY v1
where DOCUMENTTYPE IN(2) and POSTINGDATE between DATEADD(year,-1,GETDATE()) and DATEADD(year,0,GETDATE()) and DROPSHIP <> 1
and vm.ITEMNO = v1.ITEMNO and vm.LOCATIONCODE = v1.LOCATIONCODE)) as QtyOnHandInPeriod
, ABS((Select Sum(INVOICEDQTY)
from #VALUEENTRY v2
where DOCUMENTTYPE IN(2) and POSTINGDATE between DATEADD(year,-1,GETDATE()) and DATEADD(year,0,GETDATE()) and DROPSHIP <> 1 and vm.ITEMNO = v2.ITEMNO
and vm.LOCATIONCODE = v2.LOCATIONCODE)) as QtySoldInPeriod
, AVG(UNITCOST)
* ABS((Select Sum(INVOICEDQTY)
from #VALUEENTRY v3
where DOCUMENTTYPE IN(2) and POSTINGDATE between DATEADD(year,-1,GETDATE()) and DATEADD(year,0,GETDATE())
and DROPSHIP <> 1 and vm.ITEMNO = v3.ITEMNO and vm.LOCATIONCODE = v3.LOCATIONCODE)) as COGS
, Sum(INVOICEDQTY)
* AVG(UNITCOST)
/ (AVG(UNITCOST)
* ABS((Select Sum(INVOICEDQTY)
from #VALUEENTRY v4
where DOCUMENTTYPE IN(2) and POSTINGDATE between DATEADD(year,-1,GETDATE()) and DATEADD(year,0,GETDATE()) and DROPSHIP <> 1
and vm.ITEMNO = v4.ITEMNO and vm.LOCATIONCODE = v4.LOCATIONCODE))) as Turns
from #VALUEENTRY vm
where DOCUMENTTYPE IN(6) and POSTINGDATE between DATEADD(year,-1,GETDATE()) and DATEADD(year,0,GETDATE()) and DROPSHIP <> 1
GROUP BY ITEMNO, LOCATIONCODE
You could also use a CTE query to make it easier to read
WITH SumInvoiceQty AS (
SELECT ITEMNO, LOCATIONCODE, SUM(INVOICEDQTY) SumInvoiceQty
FROM #VALUEENTRY
WHERE DOCUMENTTYPE IN(2) and POSTINGDATE between DATEADD(year,-1,GETDATE()) and DATEADD(year,0,GETDATE()) and DROPSHIP <> 1
GROUP BY ITEMNO, LOCATIONCODE
),
ItemStats AS (
SELECT ITEMNO, LOCATIONCODE
,AVG(UNITCOST) as AvgUnitCost
,Sum(INVOICEDQTY) as SumInvoiceQty
FROM #VALUEENTRY v
WHERE DOCUMENTTYPE IN(6) and POSTINGDATE between DATEADD(year,-1,GETDATE()) and DATEADD(year,0,GETDATE()) and DROPSHIP <> 1
GROUP BY ITEMNO, LOCATIONCODE
)
SELECT s.ITEMNO, s.LOCATIONCODE
,s.AvgUnitCost
,s.SumInvoiceQty - ABS(q.SumInvoiceQty) as QtyOnHandInPeriod
,ABS(q.SumInvoiceQty) as QtySoldInPeriod
,AvgUnitCost * ABS(q.SumInvoiceQty) as COGS
,s.SumInvoiceQty * s.AvgUnitCost / (AvgUnitCost * ABS(q.SumInvoiceQty)) as Turns
FROM ItemStats s
INNER JOIN SumInvoiceQty q ON s.ITEMNO = q.ITEMNO and s.LOCATIONCODE = q.LOCATIONCODE;
Hope this helps
August 20, 2014 at 3:33 pm
this worked well. Thanks for the assistance!
here is the final script. I'll be adding more onto it but this was a good start.
WITH SumInvoiceQty AS
(
SELECT [Item No_], [Location Code], SUM([Invoiced Quantity]) SumInvoiceQty
FROM [50LIVE].[dbo].[Live$Value Entry]
WHERE [Document Type] IN(2) and [Posting Date] between DATEADD(year,-1,GETDATE()) and DATEADD(year,0,GETDATE()) and [Drop Shipment] <> 1
GROUP BY [Item No_], [Location Code]
),
ItemStats AS
(
SELECT [Item No_], [Location Code]
,AVG([Cost per Unit]) as AvgCostperUnit
,Sum([Invoiced Quantity]) as SumInvoiceQty
FROM [50LIVE].[dbo].[Live$Value Entry] v
WHERE [Document Type] IN(6) and [Posting Date] between DATEADD(year,-1,GETDATE()) and DATEADD(year,0,GETDATE()) and [Drop Shipment] <> 1
GROUP BY [Item No_], [Location Code]
)
/*******************************************************************************************/
SELECT s.[Item No_], s.[Location Code]
,s.AvgCostperUnit
,s.SumInvoiceQty - ABS(q.SumInvoiceQty) as QtyOnHandInPeriod
,ABS(q.SumInvoiceQty) as QtySoldInPeriod
,AvgCostperUnit * ABS(q.SumInvoiceQty) as COGS
,s.SumInvoiceQty * s.AvgCostperUnit / NULLIF((AvgCostperUnit * ABS(q.SumInvoiceQty)),0) as Turns
FROM ItemStats s
INNER JOIN SumInvoiceQty q ON s.[Item No_] = q.[Item No_] and s.[Location Code] = q.[Location Code]
August 20, 2014 at 3:44 pm
You're welcome
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply