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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

 SQL Server Forums Profile | Active Topics | Members | Search | Forum FAQ
 All Forums  SQL Server 2012 Forums  Other SQL Server 2012 Topics  Calculating weighted average cost using 2012 Reply to Topic  Printer Friendly
Author  Topic

stonebreaker
Yak Posting Veteran

USA
92 Posts

 Posted - 11/09/2012 :  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)/QtyOnHandWhere 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 WACAE0240 0.0000 0.00 BB 27.308300000000000 1.00 1.00 1.00 27.308300000000000AE0240 0.0000 4027604.00 INV 27.295000000000000 -1.00 0.00 0.00 NULLAE0240 0.0000 4069430.00 RCT 27.308300000000000 20.00 20.00 0.00 NULLAE0240 0.0000 4077505.00 VERIF 23.500000000000000 0.00 20.00 20.00 23.500000000000000AE0240 0.0000 4080158.00 INV 27.295000000000000 -2.00 18.00 0.00 NULLAE0240 0.0000 4082558.00 INV 27.295000000000000 -1.00 17.00 0.00 NULLAE0240 0.0000 4099144.00 INV 27.295000000000000 -2.00 15.00 0.00 NULL[/table]StonebreakerThe greatest obstacle to discovery is not ignorance - it is the illusion of knowledge. -Daniel Boorstin

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

 Posted - 11/11/2012 :  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

USA
92 Posts

 Posted - 11/12/2012 :  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]ASSELECT 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') GOStonebreakerThe greatest obstacle to discovery is not ignorance - it is the illusion of knowledge. -Daniel Boorstin

stonebreaker
Yak Posting Veteran

USA
92 Posts

 Posted - 11/12/2012 :  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 wJOIN [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 ORDERDECLARE @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, verifyingPartNoFROM [dbo].[vwAllTransferEvents]ORDER BY [sendingDate];OPEN curATE;FETCH NEXT FROM curATEINTO @sendingDate ,@verifyingDate ,@sendingBranch ,@receivingBranch ,@sendingEvent ,@verifyingEvent ,@sendingPartNo ,@verifyingPartNo;WHILE @@FETCH_STATUS = 0BEGIN --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 curATEINTO @sendingDate ,@verifyingDate ,@sendingBranch ,@receivingBranch ,@sendingEvent ,@verifyingEvent ,@sendingPartNo ,@verifyingPartNo;END --WHILECLOSE curATE;DEALLOCATE curATE; StonebreakerThe greatest obstacle to discovery is not ignorance - it is the illusion of knowledge. -Daniel Boorstin

stonebreaker
Yak Posting Veteran

USA
92 Posts

 Posted - 11/12/2012 :  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.StonebreakerThe greatest obstacle to discovery is not ignorance - it is the illusion of knowledge. -Daniel Boorstin

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

 Posted - 11/12/2012 :  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?
Topic
 Reply to Topic  Printer Friendly Jump To: Select Forum General SQL Server Forums       New to SQL Server Programming       New to SQL Server Administration       Script Library       Data Corruption Issues       Database Design and Application Architecture SQL Server 2012 Forums       Transact-SQL (2012)       SQL Server Administration (2012)       SSIS and Import/Export (2012)       Analysis Server and Reporting Services (2012)       Replication (2012)       Availability Groups and DR (2012)       Other SQL Server 2012 Topics SQL Server 2008 Forums       Transact-SQL (2008)       SQL Server Administration (2008)       SSIS and Import/Export (2008)       High Availability (2008)       Replication (2008)       Analysis Server and Reporting Services (2008)       Other SQL Server 2008 Topics SQL Server 2005 Forums       Transact-SQL (2005)       SQL Server Administration (2005)       .NET Inside SQL Server (2005)       SSIS and Import/Export (2005)       Service Broker (2005)       Replication (2005)       High Availability (2005)       Analysis Server and Reporting Services (2005)       Express Edition and Compact Edition (2005)       Other SQL Server Topics (2005) SQL Server 2000 Forums       SQL Server Development (2000)       SQL Server Administration (2000)       Import/Export (DTS) and Replication (2000)       Transact-SQL (2000)       Analysis Services (2000)       MSDE (2000) Development Tools       ASP.NET       Reporting Services Development       Other Development Tools Site Related Forums       Site Related Discussions       Article Discussion       Poll Discussion       The Yak Corral Other Forums       SQL Server 6.5 \ SQL Server 7.0       Other Topics       MS Access       ClearTrace Support Forum Old Forums       CLOSED - General SQL Server       CLOSED - SQL Server 2005/Yukon  -------------------- Home Active Topics Frequently Asked Questions Member Information Search Page
 SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC