SELECT TARIH,KULLANICI AS Kullanici,DEPT as DeptKodu,ADI as DepartmanAdi,AVANSTUTAR as AvansToplam,TAHSILAT as TahsilatToplam,CARITUTAR as CariHesapGelen,ODEME as KasaCikisOdemeler,DEVIRTUTAR as DevirCikisToplam,
AVANSTUTAR+TAHSILAT+CARITUTAR+ODEME+DEVIRTUTAR BakiyeToplam FROM
(
SELECT F.TARIH,F.KULLANICI,F.DEPT,D.ADI,
ISNULL((SELECT SUM(BORC) FROM FOLYOISL X WHERE X.DEPT=F.DEPT AND X.TARIH=F.TARIH AND
X.KULLANICI=F.KULLANICI AND X.ENO='AVANS' AND D.ISLEMTURU=3),0)+
ISNULL((SELECT SUM(BORC) FROM FOLYOARV X WHERE X.DEPT=F.DEPT AND X.TARIH=F.TARIH AND
X.KULLANICI=F.KULLANICI AND X.ENO='AVANS' AND D.ISLEMTURU=3),0) AVANSTUTAR,
ISNULL((SELECT SUM(BORC) FROM FOLYOISL X WHERE X.DEPT=F.DEPT AND X.TARIH=F.TARIH AND
X.KULLANICI=F.KULLANICI AND X.ENO<>'AVANS' AND X.ENO<>'DEVIR' AND X.ENO<>'ODEME' AND
 X.ENO<>'CARITAH' AND D.ISLEMTURU=3),0)+
ISNULL((SELECT SUM(BORC) FROM FOLYOARV X WHERE X.DEPT=F.DEPT AND X.TARIH=F.TARIH AND
X.KULLANICI=F.KULLANICI AND X.ENO<>'AVANS' AND X.ENO<>'DEVIR' AND X.ENO<>'ODEME' AND
 X.ENO<>'CARITAH' AND D.ISLEMTURU=3),0) TAHSILAT,
ISNULL((SELECT SUM(BORC) FROM FOLYOISL X WHERE X.DEPT=F.DEPT AND X.TARIH=F.TARIH AND
X.KULLANICI=F.KULLANICI AND X.ENO='CARITAH' AND D.ISLEMTURU=3),0)+
ISNULL((SELECT SUM(BORC) FROM FOLYOARV X WHERE X.DEPT=F.DEPT AND X.TARIH=F.TARIH AND
X.KULLANICI=F.KULLANICI AND X.ENO='CARITAH' AND D.ISLEMTURU=3),0) CARITUTAR,
ISNULL((SELECT SUM(BORC) FROM FOLYOISL X WHERE X.DEPT=F.DEPT AND X.TARIH=F.TARIH AND
X.KULLANICI=F.KULLANICI AND X.ENO='ODEME' AND D.ISLEMTURU=3),0)+
ISNULL((SELECT SUM(BORC) FROM FOLYOARV X WHERE X.DEPT=F.DEPT AND X.TARIH=F.TARIH AND
X.KULLANICI=F.KULLANICI AND X.ENO='ODEME' AND D.ISLEMTURU=3),0) ODEME,
ISNULL((SELECT SUM(BORC) FROM FOLYOISL X WHERE X.DEPT=F.DEPT AND X.TARIH=F.TARIH AND
X.KULLANICI=F.KULLANICI AND X.ENO='DEVIR' AND D.ISLEMTURU=3),0)+
ISNULL((SELECT SUM(BORC) FROM FOLYOARV X WHERE X.DEPT=F.DEPT AND X.TARIH=F.TARIH AND
X.KULLANICI=F.KULLANICI AND X.ENO='DEVIR' AND D.ISLEMTURU=3),0) DEVIRTUTAR
FROM
(SELECT TARIH,KULLANICI,DEPT FROM FOLYOISL
UNION
SELECT TARIH,KULLANICI,DEPT FROM FOLYOARV
) F
LEFT JOIN DEPART D ON (F.DEPT=D.KODU)
WHERE F.DEPT IN (SELECT KODU FROM DEPART WHERE ISLEMTURU=3)
) X
order by TARIH ASC
