FIFO / LIFO For Cost of Goods Sold

Database
N/A

FIFO / LIFO For Cost of Goods Sold

Hello,

I have to develop a cost of goods sold script using FIFO / LIFO techniques. I have a crude working program that uses volatile tables for stock in and stock out  that runs through each sale and attributes the stock using a  partitioning and row qualifying technique. The script has to be called through a macro for each sale. The problem with the technique is the more sales, the more macro executions are required. This is slow and cumbersome when dealing with large numbers of sales (1000s).

I found this example of  FIFO script that uses CTEs that purports to be faster and runs without the need for a macro: http://ask.sqlservercentral.com/questions/1227/the-subscription-list-sql-problem.html

Has anyone attempted to adapt it to Teradata SQL. If so, I would really like to know how to go about it.

Tags (2)
4 REPLIES
N/A

Re: FIFO / LIFO For Cost of Goods Sold

Maybe I should post this to the general forum?

Re: FIFO / LIFO For Cost of Goods Sold

You can try out SELECT AND CONSUME operation using QUEUE TABLE

N/A

Re: FIFO / LIFO For Cost of Goods Sold

Hi newb1,

you got luck, when i saw that competition a few years ago i tried to solve it using Teradata SQL :-)

It turned out to be much simpler due to Teradata's support of ROWS UNBOUNDED PRECEDING (Microsoft added that in SS2012):

SELECT
ArticleId
,SUM(ItemCnt) AS CurrentItems -- same as TotalStock
,SUM(ItemCnt * CurrentPrice) AS CurrentValue
FROM
(
SELECT
ArticleId

-- how many items will be used from this transaction, maybe less than all for the oldest row
,CASE WHEN RollingStock + Items > TotalStock THEN TotalStock - RollingStock ELSE Items END AS ItemCnt

-- find the latest IN-price for RET rows
,MAX(Price)
OVER (PARTITION BY ArticleID, PriceGroup
ORDER BY TranDate) AS CurrentPrice
FROM
(
SELECT
ArticleId ,TranDate ,Price ,Items --,TranCode

-- dummy column to get the current price in the next step, new group starts with every 'IN'
,SUM(CASE WHEN TranCode = 'IN' THEN 1 ELSE 0 END)
OVER (PARTITION BY ArticleID
ORDER BY TranDate
ROWS UNBOUNDED PRECEDING) AS PriceGroup

-- Aggregating all in/out movements -> number of items left in stock after all transactions
,SUM(CASE WHEN TranCode IN ('IN', 'RET') THEN Items ELSE -Items END)
OVER (PARTITION BY ArticleID) AS TotalStock

-- reverse sum of all inbound IN/RET movements
,SUM(CASE WHEN TranCode IN ('IN', 'RET') THEN Items END)
OVER (PARTITION BY ArticleID)
-SUM(CASE WHEN TranCode IN ('IN', 'RET') THEN Items END)
OVER (PARTITION BY ArticleID
ORDER BY TranDate
ROWS UNBOUNDED PRECEDING) AS RollingStock
/*
-- same as above, simpler syntax, but different ORDER BY results in extra STATS step in explain
,COALESCE(SUM(CASE WHEN TranCode IN ('IN', 'RET') THEN Items END)
OVER (PARTITION BY ArticleID
ORDER BY TranDate DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0) AS RollingStock
*/
/* -- cumulative sum, not needed to get the result
,SUM(CASE WHEN TranCode IN ('IN', 'RET') THEN Items ELSE -Items END)
OVER (PARTITION BY ArticleID
ORDER BY TranDate
ROWS UNBOUNDED PRECEDING) AS CurrentItems
*/
FROM Stock
-- only keep the row needed to calculate the value
-- plus all IN rows to find the current price for RET rows in the next step
-- to exclude items out of stock: add "AND (TotalStock > 0)"
QUALIFY ((TranCode = 'IN') OR (RollingStock <= TotalStock AND TranCode = 'RET'))AND (TotalStock > 0)
) AS dt
-- remove older IN rows
QUALIFY ItemCnt >= 0
) AS dt
GROUP BY 1
ORDER BY 1

The nested CurrentPrice calculation could be replaced using RESET WHEN in TD13.10 but nesting allows to reduce the number of rows before the 2nd STATS step:

        ,MAX(Price)
OVER (PARTITION BY ArticleID
ORDER BY TranDate
RESET WHEN price > 0
ROWS UNBOUNDED PRECEDING) AS CurrentPrice


N/A

Re: FIFO / LIFO For Cost of Goods Sold

Dieter

Thanks very much you code like a poet! I am a big fan of your work having read some of the other posts you've done in the past.

I've been trying the use of volatile tables and scalar queries to get to the same result but your SQL is far superior thanks for the help once again