问题描述

我有一个数据集，列出了未来产品库存的日期和数量.有时我们的需求会超过我们未来的供应，我们最终会得到一个负的未来数量.我需要将未来的负数量考虑到之前的供应中，这样我们就不会因过度销售供应而使问题复杂化.

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处理负值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！