SELECT TTT.CODE,CONVERT(DECIMAL(38,2),SUM(TTT.BORC)) BORC,CONVERT(DECIMAL(38,2),SUM(TTT.ALACAK)) ALACAK,CONVERT(DECIMAL(38,2),SUM(TTT.BAKIYEBORC)) BAKIYEBORC,

CONVERT(DECIMAL(38,2),SUM(TTT.BAKIYEALACAK)) BAKIYEALACAK,CONVERT(DECIMAL(38,2),SUM(TTT.BAKIYE)) BAKIYE,CONVERT(DECIMAL(38,2),SUM(TTT.DOVIZBORC)) DOVIZBORC,CONVERT(DECIMAL(38,2),SUM(TTT.DOVIZALACAK)) DOVIZALACAK,

CONVERT(DECIMAL(38,2),SUM(TTT.DOVIZBAKIYE)) DOVIZBAKIYE,CONVERT(DECIMAL(38,2),SUM(TTT.GIREN)) GIREN,CONVERT(DECIMAL(38,2),SUM(TTT.CIKAN)) CIKAN

FROM (

SELECT --GLACC.CODE ,

LEFT(GLACC.CODE,3) CODE,

SUM(GLTRN.DEBIT) BORC,

SUM(GLTRN.CREDIT) ALACAK,

CASE WHEN (SUM(GLTRN.DEBIT) - SUM(GLTRN.CREDIT))<0 THEN 0 ELSE (SUM(GLTRN.DEBIT) - SUM(GLTRN.CREDIT)) END BAKIYEBORC,

ABS(CASE WHEN (SUM(GLTRN.DEBIT) - SUM(GLTRN.CREDIT))>0 THEN 0 ELSE (SUM(GLTRN.DEBIT) - SUM(GLTRN.CREDIT)) END) BAKIYEALACAK,

(CASE WHEN (SUM(GLTRN.DEBIT) - SUM(GLTRN.CREDIT))<0 THEN 0 ELSE (SUM(GLTRN.DEBIT) - SUM(GLTRN.CREDIT)) END)-( ABS(CASE WHEN (SUM(GLTRN.DEBIT) - SUM(GLTRN.CREDIT))>0 THEN 0 ELSE (SUM(GLTRN.DEBIT) - SUM(GLTRN.CREDIT)) END) ) BAKIYE,

SUM((1 - GLTRN.SIGN) * GLTRN.REPORTNET) DOVIZBORC,

SUM(GLTRN.SIGN * GLTRN.REPORTNET) DOVIZALACAK,

( SUM((1 - GLTRN.SIGN) * GLTRN.REPORTNET))- (SUM(GLTRN.SIGN * GLTRN.REPORTNET)) DOVIZBAKIYE,

SUM((1 - GLTRN.SIGN) * GLTRN.AMNT) GIREN,

SUM(GLTRN.SIGN * GLTRN.AMNT) CIKAN

/*,SUM(GLTRN.EMUDEBIT) EMUDEBIT,

SUM(GLTRN.EMUCREDIT) EMUCREDIT*/

FROM LG_001_EMUHACC GLACC WITH (NOLOCK)

LEFT OUTER JOIN LG_001_01_EMFLINE GLTRN WITH (NOLOCK)

ON (GLACC.LOGICALREF = GLTRN.ACCOUNTREF)

WHERE

(

(GLTRN.DATE_ >= CONVERT(DATETIME, '1-1-2022', 101))

AND (GLTRN.DATE_ <= CONVERT(DATETIME, '12-31-2022', 101))

)

AND (GLTRN.TRCODE IN ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 ))

AND (GLTRN.BRANCH IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18 ))

AND (GLTRN.CANCELLED = 0)

AND (GLTRN.STATUS = 0)

AND (GLTRN.DEPARTMENT IN ( 0 ))

GROUP BY GLACC.CODE

) AS TTT

GROUP BY CODE

ORDER BY CODE