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