Expiredlots

Report ExpiredLots

Filename: ExpiredLots
Title: Lots Already expired
Sample: Unit179 (form179) and Form180.

Synopsis

List of articles/lot which are already expired.

SQL

With TempArticle AS
(
  SELECT DISTINCT
    ARTCODE
  FROM
    STOCTOTAL
  WHERE
    (ExpiryDate<=GetDate())
    and (QTYIN-isnull(X.QTYOUT,0)>0)
)
SELECT
  A.ARTCODE,
  A.LOCATION,
  A.Lotno,
  A.ExpiryDate,
  sum(QTYIN-isnull(QTYOUT,0)) AS QTY
FROM
  TempArticle B
  LEFT JOIN STOCKTOTAL A ON (B.ARTCODE=A.ARTCODE)
WHERE
  (ExpiryDate<=GetDate()
GROUP BY
  A.ARTCODE, LOCATION, Lotno, ExpiryDate
HAVING
  sum(QTYIN-isnull(QTYOUT,0))>0
ORDER BY
  A.ARTCODE, LOCATION, Lotno

Remark: Probably no need for WITH, second part should be OK ?

Parameters

None.

Accessible in forms

StockReports