用sql处理负值 [英] Handling negative values with sql
问题描述
我有一个数据集，列出了未来产品库存的日期和数量.有时我们的需求会超过我们未来的供应，我们最终会得到一个负的未来数量.我需要将未来的负数量考虑到之前的供应中，这样我们就不会因过度销售供应而使问题复杂化.
I have a data set that lists the date and quantity of future stock of products. Occasionally our demand outstrips our future supply and we wind up with a negative future quantity. I need to factor that future negative quantity into previous supply so we don't compound the problem by overselling our supply.
在以下数据集中，我需要为 10-19 的需求做好准备，方法是将负数量应用到链上，直到剩下正数量:
In the following data set, I need to prepare for demand on 10-19 by applying the negative quantity up the chain until i'm left with a positive quantity:
"ID","SKU","DATE","SEASON","QUANTITY"
"1","001","2012-06-22","S12","1656"
"2","001","2012-07-13","F12","1986"
"3","001","2012-07-27","F12","-283"
"4","001","2012-08-17","F12","2718"
"5","001","2012-08-31","F12","-4019"
"6","001","2012-09-14","F12","7212"
"7","001","2012-09-21","F12","782"
"8","001","2012-09-28","F12","2073"
"9","001","2012-10-12","F12","1842"
"10","001","2012-10-19","F12","-12159"
我需要做到这一点:
"ID","SKU","DATE","SEASON","QUANTITY"
"1","001","2012-06-22","S12","1656"
"2","001","2012-07-13","F12","152"
我已经考虑过使用 while 循环和外部应用程序，但似乎还没有找到一种方法来做到这一点.任何帮助将非常感激.这需要适用于 sql server 2008 R2.
I have looked at using a while loop as well as an outer apply but cannot seem to find a way to do this yet. Any help would be much appreciated. This would need to work for sql server 2008 R2.
这是另一个例子:
"1","002","2012-07-13","S12","1980"
"2","002","2012-08-10","F12","-306"
"3","002","2012-09-07","F12","826"
会变成:
"1","002","2012-07-13","S12","1674"
"3","002","2012-09-07","F12","826"
推荐答案
这是一个 CROSS APPLY - 已测试
Here is a CROSS APPLY - tested
SELECT b.ID,SKU,b.DATE,SEASON,QUANTITY
FROM (
SELECT SKU,SEASON, SUM(QUANTITY) AS QUANTITY
FROM T1
GROUP BY SKU,SEASON
) a
CROSS APPLY (
SELECT TOP 1 b.ID,b.Date FROM T1 b
WHERE a.SKU = b.SKU AND a.SEASON = b.SEASON
ORDER BY b.ID ASC
) b
ORDER BY ID ASC
这篇关于用sql处理负值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
