Ninsaki Ninsaki - 24 days ago 18
SQL Question

Calculating Stock movement History

I am currently trying to output Product code and Movement to show a complete history of how many of a product we have sold.

I have two tables

PUB.movement
and
PUB.product


I've made it this far

SELECT a."prod-code" AS productcode, count(*) AS Movement
FROM (SELECT mov."tran-date", pro."prod-code"
FROM PUB."movement" mov,
PUB."product" pro
WHERE mov.SKU=pro.SKU
AND mov."move-type" = 'i'
AND pro."prod-group" like 'SLA%') a
GROUP BY a."prod-code"


The output generated is:

PRODUCTCODE | MOVEMENT
0490786 1
0500012 1
0566003 1
0566004 1
0650594 1
0920127 1
0920154 1
1000557M1 1
1000578M1m 19


The only issue I have is if more than one is invoiced at that time it only counts that invoice as 1, not the quantity invoiced. There is a
qty
column in
PUB.movement
. I just can't incorporate it with the current query to output the correct stock movement.

Answer

Sounds like you want a SUM() of the qty field rather than a count of movements? Try this:

Select pro."prod-code" As productcode, Sum(mvt.qty) As Movement
  From PUB.movement As mov
  Inner Join PUB.product As pro On mov.SKU = pro.SKU
  Where mov."move-type" = 'i'
    And pro."prod-group" like 'SLA%'
  Group By pro."prod-code";
Comments