SELECT
DATEPART(YEAR, OSMA.DATE_) AS YIL,
DEFINITION_ AS BANKA_ADI,
SUM(OSMA.ALACAK) AS ALACAK,
SUM(OSMA.BORC) AS BORC,
SUM(OSMA.BAKIYE) AS BAKIYE
FROM (SELECT B.*,
CASE B.SIGN WHEN 0 THEN
(CASE B.TRCURR WHEN 0 THEN B.AMOUNT ELSE B.TRNET END)
ELSE 0 END AS BORC,
(CASE B.SIGN WHEN 1 THEN (CASE B.TRCURR WHEN 0 THEN B.AMOUNT
ELSE B.TRNET END)
ELSE 0 END) AS ALACAK,
(CASE B.SIGN WHEN 1 THEN
(CASE B.TRCURR WHEN 0 THEN B.AMOUNT
ELSE B.TRNET END)*-1
ELSE
(CASE B.TRCURR WHEN 0 THEN B.AMOUNT ELSE B.TRNET END) END )AS BAKIYE,
(CASE B.TRCURR WHEN 0 THEN 'TL'
ELSE L_CURRENCYLIST.CURCODE END) AS DOVIZ_TURU
FROM LG_607_01_BNFLINE B WITH(NOLOCK)
LEFT JOIN L_CURRENCYLIST L_CURRENCYLIST (NOLOCK)
ON L_CURRENCYLIST.CURTYPE=B.TRCURR AND L_CURRENCYLIST.FIRMNR='607'
WHERE CANCELLED=0) OSMA
LEFT OUTER JOIN LG_607_BNCARD BANKA ON
(OSMA.BANKREF = BANKA.LOGICALREF)
GROUP BY DATEPART(YEAR, OSMA.DATE_), BANKA.DEFINITION_
ORDER BY YIL, BANKA_ADI
