CODEWITHSUNDEEP
mysqlsql
Arun Gupta
Arun Gupta

Reputation: 31

Subtract Multiple Value from one Value

I have to maintain aging of Inventory where I have to show that quantity of one product is pending with Inventory department from how much days..

It will subtract on first come first basis...

Department  |Product  | Date               | Quantity  |Indicator
------------+---------+--------------------+-----------+---------
D001        |P001     | 1Jul2017           | 1000      |ADD
D001        |P001     | 10Jul2017          | 2000      |ADD
D001        |P001     | 15Jul2017          | 3000      |ADD
D001        |P001     | 18Jul2017          | 5500      |SUBTRACT
D001        |P002     | 12Jul2017          | 3000      |ADD
D001        |P002     | 14Jul2017          | 2000      |ADD
D001        |P002     | 15Jul2017          | 4000      |SUBTRACT

And the final output should be something like. if today's date is 31Jul2017

Department  |Product  | Date               | Quantity  |AgeinginDays
------------+---------+--------------------+-----------+---------
D001        |P001     | 15Jul2017          | 500       |16
D001        |P002     | 14Jul2017          | 1000      |17

Upvotes: 0

Views: 74

Answers (2)

pisi1001
pisi1001

Reputation: 89

For oracle;

SELECT 
 department,
 product,
 max(CASE WHEN "Indicator" LIKE 'Add%' THEN "Date" END) AS "Date",
 sum(CASE WHEN stock."Indicator" LIKE 'Add%' THEN (QUANTITY) ELSE (QUANTITY*-1) END) AS "QUANTITY",
 FLOOR((SYSDATE - max(CASE WHEN "Indicator" LIKE 'Add%' THEN "Date" END))) AS AgeinginDays
FROM STOCK
GROUP BY department,product

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133370

could be using aggregated function and datediff

select Department
  , Product
  , max(Date)
  , sum ( case when indicator =  'ADD' then quantity else -quantity end)
  , datediff(curdate(), max(Date))
from my_table
group by  Department, Product

or if you need the date for adding then as suggested by chillysapiens 

select Department
  , Product
  , t.max_add_date
  , sum ( case when indicator =  'ADD' then quantity else -quantity end)
  , datediff(curdate(), max(t.max_add_date))
from my_table
inner join  (
    select Department
      , Product
      , max(Date) max_add_date
    from my_table 
    where indicator = 'ADD'
    group by  Department, Product
 ) t 
group by  Department, Product

Upvotes: 2

Related Questions