首页 文章

使用SQL Server进行库存老化

提问于
浏览 次
2

我有一个表格内容如下，其中'ItemName'是项目的名称，'CurrentStock'是当前库存的项目数量，所有其他列是该年的库存和缺货的总和 . 现在我想用0替换所有负值，并用FIFO顺序中的正值调整这些负值 . 任何人都可以指导我在SQL Server中如何做到这一点 .

enter image description here

这是预期的结果
enter image description here

最初我试过这样的

select ItemName,CurrentStock, 
case when (Qnty2018+Qnty2017+Qnty2016+Qnty2015+Qnty2014+Qnty2013)<0 and (Qnty2018+Qnty2017+Qnty2016+Qnty2015+Qnty2014+Qnty2013+Qnty2012andBefore)>=0 then (Qnty2018+Qnty2017+Qnty2016+Qnty2015+Qnty2014+Qnty2013+Qnty2012andBefore) 
    when (Qnty2017+Qnty2016+Qnty2015+Qnty2014+Qnty2013)<0 and (Qnty2017+Qnty2016+Qnty2015+Qnty2014+Qnty2013+Qnty2012andBefore)>=0 THEN (Qnty2017+Qnty2016+Qnty2015+Qnty2014+Qnty2013+Qnty2012andBefore)
    when (Qnty2016+Qnty2015+Qnty2014+Qnty2013)<0 and (Qnty2016+Qnty2015+Qnty2014+Qnty2013+Qnty2012andBefore)>=0 THEN (Qnty2016+Qnty2015+Qnty2014+Qnty2013+Qnty2012andBefore)
    when (Qnty2015+Qnty2014+Qnty2013)<0 and (Qnty2015+Qnty2014+Qnty2013+Qnty2012andBefore)>=0 THEN (Qnty2015+Qnty2014+Qnty2013+Qnty2012andBefore)
    when (Qnty2014+Qnty2013)<0 and (Qnty2014+Qnty2013+Qnty2012andBefore)>=0 THEN (Qnty2014+Qnty2013+Qnty2012andBefore)
    when Qnty2013 <0 and Qnty2013+Qnty2012andBefore >=0 then Qnty2013+Qnty2012andBefore 
else Qnty2012andBefore end as Qnty2012andBefore,
case when Qnty2013<0 then 0
    when (Qnty2018+Qnty2017+Qnty2016+Qnty2015+Qnty2014)<0 and (Qnty2018+Qnty2017+Qnty2016+Qnty2015+Qnty2014+Qnty2013)>=0 then (Qnty2018+Qnty2017+Qnty2016+Qnty2015+Qnty2014+Qnty2013) 
    when (Qnty2017+Qnty2016+Qnty2015+Qnty2014)<0 and (Qnty2017+Qnty2016+Qnty2015+Qnty2014+Qnty2013)>=0 THEN (Qnty2017+Qnty2016+Qnty2015+Qnty2014+Qnty2013)
    when (Qnty2016+Qnty2015+Qnty2014)<0 and (Qnty2016+Qnty2015+Qnty2014+Qnty2013)>=0 THEN (Qnty2016+Qnty2015+Qnty2014+Qnty2013)
    when (Qnty2015+Qnty2014)<0 and (Qnty2015+Qnty2014+Qnty2013)>=0 THEN (Qnty2015+Qnty2014+Qnty2013)
    when (Qnty2014)<0 and (Qnty2014+Qnty2013)>=0 THEN (Qnty2014+Qnty2013)
else Qnty2013 end as Qnty2013,
case when Qnty2014<0 then 0
    when (Qnty2018+Qnty2017+Qnty2016+Qnty2015)<0 and (Qnty2018+Qnty2017+Qnty2016+Qnty2015+Qnty2014)>=0 then (Qnty2018+Qnty2017+Qnty2016+Qnty2015+Qnty2014) 
    when (Qnty2017+Qnty2016+Qnty2015)<0 and (Qnty2017+Qnty2016+Qnty2015+Qnty2014)>=0 THEN (Qnty2017+Qnty2016+Qnty2015+Qnty2014)
    when (Qnty2016+Qnty2015)<0 and (Qnty2016+Qnty2015+Qnty2014)>=0 THEN (Qnty2016+Qnty2015+Qnty2014)
    when (Qnty2015)<0 and (Qnty2015+Qnty2014)>=0 THEN (Qnty2015+Qnty2014)
else Qnty2014 end as Qnty2014,
case when Qnty2015<0 then 0
    when (Qnty2018+Qnty2017+Qnty2016)<0 and (Qnty2018+Qnty2017+Qnty2016+Qnty2015)>=0 then (Qnty2018+Qnty2017+Qnty2016+Qnty2015) 
    when (Qnty2017+Qnty2016)<0 and (Qnty2017+Qnty2016+Qnty2015)>=0 THEN (Qnty2017+Qnty2016+Qnty2015)
    when (Qnty2016)<0 and (Qnty2016+Qnty2015)>=0 THEN (Qnty2016+Qnty2015)
else Qnty2015 end as Qnty2015,
    case when Qnty2016<0 then 0
    when (Qnty2018+Qnty2017)<0 and (Qnty2018+Qnty2017+Qnty2016)>=0 then (Qnty2018+Qnty2017+Qnty2016) 
    when (Qnty2017)<0 and (Qnty2017+Qnty2016)>=0 THEN ( Qnty2017+Qnty2016)
else Qnty2016 end as Qnty2016,
case when Qnty2017<0 then 0
    when (Qnty2018)< 0 and (Qnty2018+Qnty2017)>=0 then (Qnty2018+Qnty2017) 
else Qnty2017 end as Qnty2017,
case when Qnty2018<0 then 0 else Qnty2018 end as Qnty2018 FROM StockTable

但它给出了错误的输出
enter image description here

谢谢

sql sql-server stock inventory

2 回答

  • 0

    可能有两种解决方案 .

    • 使用#Temp表，如下所示
    SELECT ItemName,CurrentStock,
CASE when Qnty2018<0 THEN 0 ELSE Qnty2018 END as Qnty2018,
CASE when Qnty2017<0 THEN 0 ELSE Qnty2017 END as Qnty2017,
CASE when Qnty2016<0 THEN 0 ELSE Qnty2016 END as Qnty2016,
CASE when Qnty2015<0 THEN 0 ELSE Qnty2015 END as Qnty2015,
CASE when Qnty2014<0 THEN 0 ELSE Qnty2014 END as Qnty2014,
CASE when Qnty2013<0 THEN 0 ELSE Qnty2013 END as Qnty2013,
CASE when Qnty2012andBefore<0 THEN 0 ELSE Qnty2012andBefore END as Qnty2012andBefore INTO #Temp FROM StockTable

update #Temp set Qnty2018=CurrentStock WHERE Qnty2018<>0 AND Qnty2017=0 AND Qnty2016=0 AND Qnty2015=0 AND Qnty2014=0 AND Qnty2013=0 AND Qnty2012andBefore=0
update #Temp set Qnty2017=CurrentStock-Qnty2018 WHERE Qnty2017<>0 AND Qnty2016=0 AND Qnty2015=0 AND Qnty2014=0 AND Qnty2013=0 AND Qnty2012andBefore=0
update #Temp set Qnty2016=CurrentStock-Qnty2018+Qnty2017 WHERE Qnty2016<>0 AND Qnty2015=0 AND Qnty2014=0 AND Qnty2013=0 AND Qnty2012andBefore=0
update #Temp set Qnty2015=CurrentStock-Qnty2018+Qnty2017+Qnty2016 WHERE Qnty2015<>0 AND Qnty2014=0 AND Qnty2013=0 AND Qnty2012andBefore=0
update #Temp set Qnty2014=CurrentStock-Qnty2018+Qnty2017+Qnty2016+Qnty2015 WHERE Qnty2014<>0 AND Qnty2013=0 AND Qnty2012andBefore=0
update #Temp set Qnty2013=CurrentStock-Qnty2018+Qnty2017+Qnty2016+Qnty2015+Qnty2014 WHERE Qnty2013<>0 AND Qnty2012andBefore=0
update #Temp set Qnty2012andBefore=CurrentStock-Qnty2018+Qnty2017+Qnty2016+Qnty2015+Qnty2014+Qnty2013 WHERE Qnty2012andBefore<>0
SELECT * FROM #Temp

    2.使用Cursor，你可以在行的基础上迭代并编写逻辑 . 因为在SQL中不建议这样做，所以只有在没有其他选项时才使用它 .

    回复于
  • 0

    你认为你需要 CASE 中的总和是什么？举个例子，在我看来，每个列上的一个 CASE 都有你想要的效果 . 对于 Qnty2012andBefore 的计算，再次从 CurrentStock 中减去，并在求和上单独应用 CASE . 像那样：

    SELECT ItemName,
       CurrentStock,
       CASE
         WHEN Qnty2018 < 0
           THEN 0
         ELSE
           Qnty2018
       END Qnty2018,
       ...
       CASE
         WHEN Qnty2013 < 0
           THEN 0
         ELSE
           Qnty2013
       END Qnty2013,
       CurrentStock
       -
       CASE
         WHEN Qnty2018 < 0
           THEN 0
         ELSE
           Qnty2018
       END
       ...
       -
       CASE
         WHEN Qnty2013 < 0
           THEN 0
         ELSE
           Qnty2013
       END Qnty2012andBefore
       FROM StockTable;
    回复于

相关问题