SET DATEFORMAT DMY
SELECT I.CODE, I.NAME,
SUM(CASE WHEN S.TRCODE IN (2,3) THEN -L.AMOUNT ELSE 0 END) AS IADE,
SUM(CASE WHEN S.TRCODE IN (7,8) THEN L.AMOUNT ELSE 0 END) AS SATIS,
SUM(CASE WHEN S.TRCODE IN (2,3) THEN -L.AMOUNT ELSE L.AMOUNT END) AS NETSATIS,
SUM(CASE WHEN S.TRCODE IN (2,3) THEN -L.VATMATRAH ELSE 0 END) AS IADE_TL,
SUM(CASE WHEN S.TRCODE IN (7,8) THEN L.VATMATRAH ELSE 0 END) AS SATIS_TL,
SUM(CASE WHEN S.TRCODE IN (2,3) THEN -L.VATMATRAH ELSE L.VATMATRAH END) AS NET_SATIS_TL,
SUM(CASE WHEN S.TRCODE IN (2,3) THEN -(L.VATMATRAH/L.REPORTRATE) ELSE 0 END) AS IADE_EURO,
SUM(CASE WHEN S.TRCODE IN (7,8) THEN (L.VATMATRAH/L.REPORTRATE) ELSE 0 END) AS SATIS_EURO,
SUM(CASE WHEN S.TRCODE IN (2,3) THEN -(L.VATMATRAH/L.REPORTRATE) ELSE (L.VATMATRAH/ L.REPORTRATE) END) AS NET_SATIS_EURO
FROM LG_xxx_01_STFICHE S
LEFT JOIN LG_xxx_xx_STLINE L ON (S.LOGICALREF = L.STFICHEREF)
LEFT JOIN LG_xxx_CLCARD C ON (C.LOGICALREF = S.CLIENTREF)
LEFT JOIN LG_xxx_ITEMS I ON (L.STOCKREF = I.LOGICALREF)
WHERE S.TRCODE IN (2,3,7,8) AND
S.DATE_='TARIH'
GROUP BY I.CODE, I.NAME ORDER BY I.CODE
