StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Your post is a few months old, but nevertheless this might be useful for others, too.</p> <p>There was a similar question on Teradata's Developer Exchange, when i remembered that i ported it to Teradata a few years ago. A quick search for other solutions routed me to this post.</p> <p>It turned out to be much simpler due to Teradata's support of ROWS UNBOUNDED PRECEDING (Microsoft added that in SS2012):</p> <p>Regarding your questions:</p> <p>a: CTE can be replaced by Derived Tables, it's just a syntax variation.</p> <p>b: CROSS/OUTER APPLY is SQL Server proprietary syntax, which can sometimes be replaced by an [OUTER] JOIN, in this case it was just a complicated way to do a cumulative sum.</p> <p>c: Index hints should be a last resort when the optimizer is not doing a good plan</p> <pre><code>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 </code></pre> <p>It's based on the same logic as the winning solution described here: <a href="https://www.simple-talk.com/sql/performance/set-based-speed-phreakery-the-fifo-stock-inventory-sql-problem/" rel="nofollow">https://www.simple-talk.com/sql/performance/set-based-speed-phreakery-the-fifo-stock-inventory-sql-problem/</a></p> <p>This will be running quite fast and you don't have to create any of the indexes needed for SQL Server :-)</p> <p>Remark for porting it to other DBMSes:</p> <p>It's plain Standard SQL, only the QUALIFY is Teradata specific. QUALIFY is the same as a HAVING for GROUP BY, filtering the result of an OLAP function. It can be easily replaced by moving the condition into a WHERE in the outer level.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload