Search code examples
sqlsql-serverstockinventory

Stock ageing with SQL Server

I have a table content as below, where 'ItemName' is the name of the items, 'CurrentStock' is the current stock quantity of items and all other columns are the sum of stock-in and stock-out on that year. Now I want to replace all negative value with 0 and adjust those negative value with the positive value in FIFO order. Can any one guide me how do that in SQL Server.

enter image description here

Here is the Expected Result enter image description here

Initially I tried like this

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

but it gave wrong output like this enter image description here

Thanks

Solution

  • There could be two solutions for this.

    1. Using #Temp table as shown below

      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.Using Cursor, You could iterate on row basis and write the logic. since this is not recommended in SQL, use it only if you don't have other option.