Calculating weighted average cost using 2012

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Other SQL Server 2012 Topics

Calculating weighted average cost using 2012

Author

Topic

stonebreaker
Yak Posting Veteran

92 Posts

Posted - 2012-11-09 : 16:19:23

I'm trying to calculate a perpetual weighted average cost for inventory using the new windowing functions (sum() over (partition by...))

For periodic Weighted Average Cost, this is easy:
SUM(Qty*UnitPrice) OVER (PARTITION BY [BRANCH],[PARTNO],[SKEY] ORDER BY [EventDateTime]
/SUM(Qty) OVER (PARTITION BY [BRANCH],[PARTNO],[SKEY] ORDER BY [EventDateTime]

For perpetual calculations, not so easy. The accounting formula is as follows:
((QtyOnHand-QtyOrdered)*PreviousWeightedAvgCost)+(QtyOrdered*UnitPriceOrdered)
/QtyOnHand

Where QtyOnHand = previous stock plus the ordered quantity.

A new WAC is not generated for every event in inventory. It is only recalculated when a new order comes in, and for other event types such as sales and transfers, the last calculated WAC is carried forward. Is there any way to carry the WAC value forward until another one is generated?

[table]
PARTNO SKEY EVENTNO WacEventType UnitCost QTY QTYONHAND VerifQty WAC
AE0240 0.0000 0.00 BB 27.308300000000000 1.00 1.00 1.00 27.308300000000000
AE0240 0.0000 4027604.00 INV 27.295000000000000 -1.00 0.00 0.00 NULL
AE0240 0.0000 4069430.00 RCT 27.308300000000000 20.00 20.00 0.00 NULL
AE0240 0.0000 4077505.00 VERIF 23.500000000000000 0.00 20.00 20.00 23.500000000000000
AE0240 0.0000 4080158.00 INV 27.295000000000000 -2.00 18.00 0.00 NULL
AE0240 0.0000 4082558.00 INV 27.295000000000000 -1.00 17.00 0.00 NULL
AE0240 0.0000 4099144.00 INV 27.295000000000000 -2.00 15.00 0.00 NULL
[/table]

Stonebreaker
The greatest obstacle to discovery is not ignorance - it is the illusion of knowledge.
-Daniel Boorstin

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-11 : 07:46:17

This sounds like ideally suited for the windowing functions in SQL 2012. Can you post the results that you are expecting for a representative sample input? I say "representative sample" because you indicated that weighted average cost is calculated only "when a new order comes in, and for other event types such as sales and transfers..." Can you include data that demonstrates such rules as well?

stonebreaker
Yak Posting Veteran

92 Posts

Posted - 2012-11-12 : 08:51:17

Here is the first view I use to calculate the weighted average cost. Notice the final WHERE clause.

CREATE VIEW [dbo].[WAC]
AS

SELECT
BRANCH
,[EventDateTime]
,[TS]
,PARTNO
,SKEY
,EVENTNO
,[WacEventType]
,[UnitCost]
,[QTY]
,[QTYONHAND]
,[VerifQty]
,CASE
WHEN SUM([VerifQty]) OVER (PARTITION BY BRANCH,PARTNO,SKEY ORDER BY [EventDateTime] ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) IS NULL
THEN [UnitCost] -- BEGINNING BALANCE
WHEN [QTYONHAND] <= [VerifQty]
THEN [UnitCost]
ELSE (((SUM([UnitCost]*[VerifQty]) OVER (PARTITION BY BRANCH,PARTNO,SKEY ORDER BY [EventDateTime] ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
/SUM([VerifQty]) OVER (PARTITION BY BRANCH,PARTNO,SKEY ORDER BY [EventDateTime] ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING))
*([QTYONHAND]-[VerifQty])) -- previous extended cost
+([UnitCost]*[VerifQty])) -- current extended cost
/[QTYONHAND]
END AS [WAC]
FROM
(SELECT
BRANCH
,[EventDateTime]
,[TS]
,PARTNO
,SKEY
,EVENTNO
,[WacEventType]
,[UnitCost]
,QTY
,SUM(QTY) OVER (PARTITION BY BRANCH,PARTNO,SKEY
ORDER BY [EventDateTime]) AS QTYONHAND
,[VerifQty]
--INTO #QtyOnHand
FROM
(SELECT [Branch]
,[PartNo]
,[SKey]
,'B' AS [TS]
,0 AS [EVENTNO]
,[EventDate] AS [EventDateTime]
,'BB' AS [WacEventType]
,[WACCostLastOfPeriod]AS [UnitCost]
,[QtyLastOfPeriod] AS [Qty]
,[QtyLastOfPeriod] AS [VerifQty]
FROM [dbo].[PeriodWACBalance]
WHERE [EVENTDATE] = '2011-12-31'
UNION
SELECT
[BRANCH]
,[PARTNO]
,CASE
WHEN [TS] = 'S'
THEN 0
ELSE [SKEY]
END AS [SKEY]
,[TS]
,[EVENTNO]
,[EventDateTime]
,[WacEventType]
,CASE
WHEN [ev].[WacEventType] IN ('DVIV','DVIB','VERIF')
THEN [ev].[COSTVER]
WHEN [ev].[WacEventType] IN ('TRIN')
THEN COALESCE([ev].[WacCost], [ev].[COSTVER]) -- get transfer wac if it has been calculated, else use COSTVER
WHEN [ev].[WacEventType] IN ('RCT','TNRCT')
THEN [ev].[COSTBEG]
WHEN [ev].[WacEventType] IN ('INV','TROUT')
THEN [ev].[COSTINV]
WHEN [ev].[WacEventType] IN ('MVOUT','MVOTR','MVIN','MVITR')
THEN [ev].[COSTMOV]
WHEN [ev].[WacEventType] IN ('RECON')
THEN [ev].[COSTRECON]
WHEN [ev].[WacEventType] IN ('ADJ')
THEN ([ev].[EXTADJ]/[ev].[QTYADJ])
ELSE 0
END AS [UnitCost]
,CASE
WHEN [ev].[WacEventType] IN ('DVIV','DVIB')
THEN 0 --[ev].[QtyVer]
WHEN [ev].[WacEventType] IN ('RCT','TNRCT')
THEN [ev].[QTYREC]
WHEN [ev].[WacEventType] IN ('INV','TROUT')
THEN [ev].[QTYINV] *(-1)
WHEN [ev].[WacEventType] IN ('MVOUT','MVOTR')
THEN [ev].[QTYMOV]
WHEN [ev].[WacEventType] IN ('MVIN','MVITR')
THEN [ev].[QTYMOV]
WHEN [ev].[WacEventType] IN ('RECON')
THEN [ev].[QTYRECON]
WHEN [ev].[WacEventType] IN ('ADJ')
THEN [ev].[QTYADJ]
WHEN [ev].[WacEventType] IN ('VERIF','TRIN')
THEN 0 --[ev].[QTYVER]
ELSE 0
END AS [Qty]
,CASE
WHEN [ev].[WacEventType] IN ('DVIV','DVIB')
THEN [ev].[QtyVer]
WHEN [ev].[WacEventType] IN ('VERIF','TRIN')
THEN [ev].[QTYVER]
ELSE 0
END AS [VerifQty]
FROM [dbo].[events] AS ev
) AS v
) AS z

WHERE [WacEventType] IN ('VERIF','TRIN','DVIV','DVIB','BB')

GO

Stonebreaker
The greatest obstacle to discovery is not ignorance - it is the illusion of knowledge.
-Daniel Boorstin

stonebreaker
Yak Posting Veteran

92 Posts

Posted - 2012-11-12 : 09:09:07

Most of the case statements in the above derived table are due to the base data not being completely normalized.

Background: We often receive parts from a vendor and don't get the actual invoice price until later, so we have price verification events where this is corrected. They are noted in the final where clause, WHERE [WacEventType] IN ('VERIF','TRIN','DVIV','DVIB','BB'). This puts the quantity being verified into the VerifQty column.

The complication comes in where we transfer parts between branches before the verification event comes in to the first branch. Because they come in to the receiving branch with the wrong unit price, I need to update the unit price with the WAC from the sending branch, so that the WAC at the receiving branch will be correct. I have another view that gives me a list of transfer events, and links them back to the associated transfer out (WacEventType = 'TROUT') event in the sending branch. The TROUT event does not currently have the correct WAC value, so I have to use the following script to update the transfer in ('TRIN') event:

/*
SELECT COUNT(*)
FROM [dbo].[WAC] AS w
JOIN [dbo].[vwAllTransferEvents] AS a ON a.[receivingBranch] = w.[BRANCH]
AND a.[verifyingEvent] = w.[EVENTNO]
*/

-- CREATE CURSOR FOR [dbo].[vwAllTransferEvents]
-- UPDATE [dbo].[events] 'TRIN' EVENTS IN DATETIME ORDER

DECLARE
@sendingDate DATETIME
,@verifyingDate DATETIME
,@sendingBranch VARCHAR(3)
,@receivingBranch VARCHAR(3)
,@sendingEvent DECIMAL(12,2)
,@verifyingEvent DECIMAL(12,2)
,@sendingPartNo VARCHAR(7)
,@verifyingPartNo VARCHAR(7)

DECLARE curATE CURSOR
FOR SELECT sendingDate, verifyingDate, sendingBranch, receivingBranch, sendingEvent, verifyingEvent, sendingPartNo, verifyingPartNo
FROM [dbo].[vwAllTransferEvents]
ORDER BY [sendingDate];

OPEN curATE;

FETCH NEXT FROM curATE
INTO @sendingDate
,@verifyingDate
,@sendingBranch
,@receivingBranch
,@sendingEvent
,@verifyingEvent
,@sendingPartNo
,@verifyingPartNo;

WHILE @@FETCH_STATUS = 0
BEGIN --WHILE

MERGE INTO [dbo].[events] AS tgt
USING (SELECT [w].[BRANCH]
,[w].[PARTNO]
,[w].[SKEY]
,[w].[WAC]
,[w].[EVENTNO]
FROM [dbo].[WAC] AS w
JOIN ( SELECT [b].[BRANCH]
,[b].[PARTNO]
,[b].[SKEY]
,MAX([b].[EVENTNO]) AS [EVENTNO]
FROM [dbo].[WAC] AS b
WHERE [b].[BRANCH] = @sendingBranch
AND [b].[PARTNO] = @sendingPartNo
GROUP BY [b].[BRANCH]
,[b].[PARTNO]
,[b].[SKEY]
HAVING MAX([EVENTNO]) < @sendingEvent
) AS a ON [a].[BRANCH] = [w].[BRANCH]
AND [a].[EVENTNO] = [w].[EVENTNO]
) AS src
ON [tgt].[BRANCH] = [src].[BRANCH]
AND [tgt].[EVENTNO] = [src].[EVENTNO]
WHEN MATCHED AND [tgt].[WacCost] IS NULL
THEN UPDATE
SET [WacCost] = [src].[WAC];

FETCH NEXT FROM curATE
INTO @sendingDate
,@verifyingDate
,@sendingBranch
,@receivingBranch
,@sendingEvent
,@verifyingEvent
,@sendingPartNo
,@verifyingPartNo;

END --WHILE

CLOSE curATE;
DEALLOCATE curATE;

Stonebreaker
The greatest obstacle to discovery is not ignorance - it is the illusion of knowledge.
-Daniel Boorstin

stonebreaker
Yak Posting Veteran

92 Posts

Posted - 2012-11-12 : 09:15:27

Notice in the above MERGE that I have to join the WAC view to itself in order to get the correct event. If I could pass the WAC to all rows of the WAC view, then I wouldn't need to do the self-join and hopefully would speed this up a bit.

The events table has about 9 million rows, and the transfer events view that I am cursoring through has about 270,000 rows.

Any help or suggestions to improve performance would be greatly appreciated.

Stonebreaker
The greatest obstacle to discovery is not ignorance - it is the illusion of knowledge.
-Daniel Boorstin

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-12 : 10:33:02

This is a lot of code for someone to read and understand. I was hoping you would distill it down and post in a manner that illustrates the windowing function use that you are trying to do. Also, in a form that someone can copy and paste - for example like this (I am making this up, so it is not intented to be the correct representation of your problem).

Sample input data

CREATE TABLE #WACTest
(
	ID int not null identity(1,1),
	PARTNO           VARCHAR(32),
	WacEventType     VARCHAR(32),
	UnitCost         FLOAT,
	QTY              FLOAT,
	QTYONHAND        FLOAT,
	WAC              FLOAT
)

INSERT INTO #WACTest VALUES
('AE0240','BB','27.308300000000000','1.00','1.00','27.308300000000000'),
('AE0240','INV','27.295000000000000','-1.00','0.00',NULL),
('AE0240','RCT','27.308300000000000','20.00','20.00',NULL),
('AE0240','VERIF','23.500000000000','0.00','20.00','23.500000000000000'),
('AE0240','INV','27.295000000000000','-2.00','18.00',NULL),
('AE0240','INV','27.295000000000000','-1.00','17.00',NULL),
('AE0240','INV','27.295000000000000','-2.00','15.00',NULL)

Required Output (last column is what needs to be calculated):

1	AE0240	BB	27.3083	1	1	27.3083
2	AE0240	INV	27.295	-1	0	NULL
3	AE0240	RCT	27.3083	20	20	27.308965
4	AE0240	VERIF	23.5	0	20	27.308965
5	AE0240	INV	27.295	-2	18	27.3105166666667
6	AE0240	INV	27.295	-1	17	27.3114294117647
7	AE0240	INV	27.295	-2	15	27.31362

The code that was tried is this

select
	*,
	sum(unitcost*qty) over(order by Id) 
	/ 
	nullif (sum (qty) over (order by id),0) as WAC
from
	#WACTest

Can you post a simplified version such as this so someone can easily understand the issue you are facing while having to understand only the minimum required details of your business problem?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources