Stockcompare
Report StockCompare
- Filename: StockCompare
- Title: Stock vergelijking voor :Supplier
- Sample: Unit145
Synopsis
Display a list of differences between own stock and supplier stock.
SQL
WITH TempData as (
SELECT
Artcode, sum(QTYIN-isnull(QTYOUT, 0)) AS QTY
FROM
STOCTOTAL_KIT
WHERE
(STOCKCODE='75') and (SUPPLIER=:Supplier)
GROUP BY ARTCODE
HAVING sum(QTYIN-isnull(QTYOUT, 0))>0
)
SELECT
A.Artcode,
A.QTY,
isnull(SUM(B.QTY), 0) AS QTYX,
NAME2
FROM
TempData A
LEFT JOIN STOCKCOMPARE B ON
(A.ARTCODE=B.ARTCODE)
AND (B.COmpareID=:CompareID)
INNER JOIN ARTICLE D ON (A.ARTCODE=D.NAME)
GROUP BY
A.ARTCODE, A.QTY, NAME2
HAVING
A.QTY<>isnull(SUM(B.QTY), 0)
UNION
SELECT
A.ARTCODE,
0 AS QTY,
sum(A.QTY) AS QTYX,
NAME2
FROM
STOCKCOMPARE A
LEFT JOIN ARTICLE D ON (A.ARTCODE=D.NAME)
WHERE
(A.COmpareID=:CompareID)
AND (A.ARTCODE NOT IN (SELECT C.ARTCODE FROM TEMPDATA C))
GROUP BY
A.ARTCODE, NAME2
UNION
SELECT
A.ARTCODE, sum(A.QTY) AS QTY, 0 AS QTYX, NAME2
FROM
TEMPDATA A
LEFT OUTER JOIN ARTICLE D ON (A.ARTCODE=D.NAME)
WHERE
A.ARTCODE NOT IN (SELECT C.ARTCODE
FROM STOCKCOMPARE C
WHERE C.CompareID=:CompareID)
GROUP BY
A.ARTCODE, NAME2
ORDER BY A.ARTCODE
Parameters
- CompareID The job ID of the job to be compared.
- Supplier Supplier code.