Merhabalar arkadaşlar büyük bir hata alıyorum çözümünü hiç bir türlü bulamadım yardımcı olursanız çok sevinirim.
Aşağıdaki Kodda WHERE I.ACTIVE = 0 AND I.CARDTYPE = 1 filtrelemeyi LEFT JOIN aa as cG ON cG.KOD = I.LOGICALREF bunu eklediğim zaman yapmıyor çok saçma hiç bir çözüm bulamadım.

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_123_01_STLINE S
WHERE S.TRCODE = 1 AND S.LINETYPE = 0 AND S.CANCELLED = 0
      AND S.DATE_ = (SELECT MAX(DATE_) FROM LG_123_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_123_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_123_01_STLINE S left join LG_123_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_123_ITEMS as I
LEFT JOIN LV_123_01_GNTOTST G ON G.STOCKREF = I.LOGICALREF AND G.INVENNO = -1
LEFT JOIN LG_123_PRCLIST P ON P.CARDREF = I.LOGICALREF AND P.PTYPE = 2 AND P.CLSPECODE = 'T01' AND P.ACTIVE = 0
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