--KOD
Select CL.CODE as Kodu,
CL.DEFINITION_ as Acıklama,
Date_ as Tarih,
Onceki_bakiye =
(SELECT SUM(CASE WHEN IC_CLFLINE.SIGN=0 THEN AMOUNT ELSE AMOUNT*-1 END)
FROM LG_050_02_CLFLINE IC_CLFLINE
WHERE CLFLINE.CLIENTREF=IC_CLFLINE.CLIENTREF
AND IC_CLFLINE.DATE_<CLFLINE.DATE_ AND IC_CLFLINE.CANCELLED=0),
TL=SUM(CASE CLFLINE.TRCURR WHEN 0 THEN (TRNET-(SIGN*TRNET))-(SIGN*TRNET) ELSE 0 END) ,
USD=SUM(CASE CLFLINE.TRCURR WHEN 1 THEN (TRNET-(SIGN*TRNET))-(SIGN*TRNET) ELSE 0 END) ,
EURO=SUM(CASE CLFLINE.TRCURR WHEN 20 THEN (TRNET-(SIGN*TRNET))-(SIGN*TRNET) ELSE 0 END),
--Borc=SUM(Case When CLFLINE.SIGN=0 THEN AMOUNT ELSE 0 END),
Satis_Faturasi=SUM(Case When CLFLINE.MODULENR=4 AND CLFLINE.TRCODE IN(37,38) THEN AMOUNT ELSE 0 END),
--Alacak=SUM(CASE WHEN CLFLINE.SIGN=1 THEN AMOUNT ELSE 0 END),
Tahsilat=SUM(Case When TRCODE =1 THEN AMOUNT ELSE 0 END),
Son_Bakiye=
(SELECT SUM(CASE WHEN IC_CLFLINE.SIGN=0 THEN AMOUNT ELSE AMOUNT*-1 END)
FROM LG_050_02_CLFLINE IC_CLFLINE
WHERE CLFLINE.CLIENTREF=IC_CLFLINE.CLIENTREF
AND IC_CLFLINE.DATE_<CLFLINE.DATE_
AND IC_CLFLINE.CANCELLED=0)+ SUM(Case When CLFLINE.SIGN=0 THEN AMOUNT ELSE 0 END)- SUM(CASE WHEN CLFLINE.SIGN=1 THEN AMOUNT ELSE 0 END)
FROM LG_050_CLCARD CL LEFT JOIN LG_050_02_CLFLINE CLFLINE ON CL.LOGICALREF=CLFLINE.CLIENTREF
WHERE DATE_ = '2015.01.08'
AND CL.CODE LIKE 'MST%'
AND CL.CODE NOT LIKE 'MST_T%'
GROUP BY CLFLINE.CLIENTREF,CODE,DEFINITION_,DATE_
ORDER BY USD DESC,EURO DESC,TL DESC