SELECT
(select top(1) cast(SUM(TOTAL*cast(DATE_ as int) )/ (SUM(TOTAL))as datetime) FROM LG_008_04_PAYTRANS) AS ISTENILENALAN,
CLCARD.CODE AS KODU, CLCARD.CITY AS ŞEHİR, CLCARD.DEFINITION_ AS ÜNVANI, CLCARD.SPECODE AS PLASİYER,
CASE WHEN SUM(CASE WHEN SIGN = 0 THEN 1 ELSE - 1 END * CLFLINE.AMOUNT) >= 0 THEN SUM(CASE WHEN SIGN = 0 THEN 1 ELSE - 1 END * CLFLINE.AMOUNT)
ELSE 0 END AS BAKIYE_BORC, CASE WHEN SUM(CASE WHEN SIGN = 0 THEN 1 ELSE - 1 END * CLFLINE.AMOUNT)
< 0 THEN SUM(CASE WHEN SIGN = 0 THEN 1 ELSE - 1 END * CLFLINE.AMOUNT) ELSE 0 END AS BAKIYE_ALACAK
FROM LG_008_04_CLFLINE AS CLFLINE INNER JOIN
LG_008_CLCARD AS CLCARD ON CLFLINE.CLIENTREF = CLCARD.LOGICALREF AND CLFLINE.CANCELLED = 0
GROUP BY CLCARD.CODE, CLCARD.CITY, CLCARD.SPECODE, CLCARD.DEFINITION_
HAVING (CLCARD.CODE LIKE 'A.N%')
ORDER BY ÜNVANI