CODEWITHSUNDEEP
mysqlstock
Uğur PARLAYAN
Uğur PARLAYAN

Reputation: 23

MySQL how to calculate after stock taking

Data tables in the following manner;

Firm | PartID | StockCount |       Date | Type
-----------------------------------------------------------
   1 |     71 |          5 | 2014-02-01 | Incoming Invoice
   1 |     71 |         -1 | 2014-02-09 | Send Invoice
   1 |     71 |         10 | 2014-02-13 | Stocktaking  ( !!! Of the Depot. )
   1 |     71 |         -1 | 2014-02-21 | Send Invoice
   1 |     71 |          5 | 2014-02-28 | Incoming Invoice

This table is actually a stock is a fictionalized depiction of movement table. Counts in this table made ​​in the store, purchase and sales invoices include. In this way, entering the warehouse, from the warehouse and the warehouse will be collected in a table quantities actually counted. From the moment that made ​​the Census, stock values ​​should be calculated over the stated amount. Where the problem was coming.

How do I get the following result? 

Firm | PartID | StockCount |       Date | Type
-------------------------------------------------------
   1 |     71 |         14 |       NULL | NULL

Upvotes: 1

Views: 607

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1271171

You seem to want the sum of the stock after "stock taking", which I suspect is more normally called "doing inventory" in English.

select Firm, PartId, sum(StockCount) as StockCount, NULL as Date, NULL as Type
from table t
where Date >= (select max(Date)
               from table t2
               where t2.Firm = t.Firm and
                     t2.partid = t.partid and
                     t2.type = 'Stocktaking'
              )
group by Firm, Partid;

If there may be no Stocktaking record, then go with a left join approach:

select Firm, PartId, sum(StockCount) as StockCount, NULL as Date, NULL as Type
from table t left join
     (select Firm, PartId, max(Date) as maxDate
      from table t
      where t2.type = 'Stocktaking'
      group by Firm, PartId
     ) as tfp
     on t.Firm = tfp.Firm and t.PartId = tfp.PartId and t.Date >= tfp.MaxDate
group by t.Firm, t.PartId;

Upvotes: 1

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33391

Your explanation of the problem is unclear, but I assume it is what you want.

SELECT Firm, PartID, SUM(StockCount) as StockCount, NULL as Date, NULL as Type
FROM tbl T1
WHERE Date >= (SELECT Date FROM tbl T2
                  WHERE T2.Type = Stocktaking
                   AND T1.Firm =T2.Firm
                   AND T1.PartId = T2.PartId
              )
GROUP BY Firm, PartID

Upvotes: 0

underscore
underscore

Reputation: 6887

Try this 

SELECT Firm,
       PartID,
       Count(StockCount),
       Date AS NULL,
       TYPE AS NULL
FROM TABLE
GROUP BY Firm,
         PartID

Upvotes: 0

n34_panda
n34_panda

Reputation: 2677

If I am reading it right:

SELECT firm, partid, count(stockCount) as stock_total
FROM yourtable
WHERE firm = 1
AND partid = 71

You'll need to group by if you want to select multiple parts, something like:

SELECT firm, partid, count(stockCount) as stock_total
FROM yourtable
WHERE firm = 1
GROUP BY partid

Upvotes: 0

Related Questions