WITH LastDate AS (
     SELECT S.STOCKREF,
       ISNULL(MAX(S.DATE_),'2022-01-01 00:00:00.000') AS MAX_DATE,
       S.AMOUNT AS adet
FROM LG_001_01_STLINE S
WHERE S.TRCODE = 1 AND S.LINETYPE = 0 AND S.CANCELLED = 0
      AND S.DATE_ = (SELECT MAX(DATE_) FROM LG_001_01_STLINE WHERE STOCKREF = S.STOCKREF AND TRCODE = 1 AND LINETYPE = 0 AND CANCELLED = 0)
GROUP BY S.STOCKREF, S.AMOUNT
), aa AS (
  SELECT
    distinct(S.STOCKREF) as KOD,
    DATEDIFF(day,MAX(S.DATE_),GETDATE()) AS ISLEMGORMEYENGUN,
    MAX(S.DATE_) as SONSATIS,
    S.AMOUNT AS adet
FROM LG_001_01_STLINE S
WHERE S.TRCODE = 8 AND S.CANCELLED = 0 AND S.INVOICEREF <> 0
GROUP BY S.STOCKREF,S.AMOUNT
),
 DEVIR AS (
SELECT S.STOCKREF as STOCKREF,SUM(S.AMOUNT) as Devir FROM LG_001_01_STLINE S left join LG_001_ITEMS I on I.LOGICALREF = S.STOCKREF WHERE I.ACTIVE = 0 AND I.CARDTYPE = 1 AND S.LINETYPE =0 AND S.TRCODE = 14 AND S.CANCELLED =0  GROUP BY S.STOCKREF
)
SELECT
  I.LOGICALREF,
  I.CARDTYPE,
  I.ACTIVE,
  I.STGRPCODE,
  I.KEYWORD5,
  I.B2CCODE,
  I.CODE,
  I.NAME,
  I.IMAGEINC,
  G.ONHAND AS STOK
FROM LG_001_ITEMS as I
LEFT JOIN LV_001_01_GNTOTST G ON G.STOCKREF = I.LOGICALREF
LEFT JOIN LG_001_PRCLIST P ON P.CARDREF = I.LOGICALREF
LEFT JOIN DEVIR as DV on DV.STOCKREF = I.LOGICALREF
LEFT JOIN LastDate as LD on LD.STOCKREF = I.LOGICALREF
LEFT JOIN aa as cG ON cG.KOD = I.LOGICALREF
WHERE I.ACTIVE = 0 AND I.CARDTYPE = 1 AND P.CLSPECODE = 'T01' AND P.ACTIVE = 0 AND P.PTYPE = 2  AND G.INVENNO = -1