Başlangıç için birşeyler yaptım.
Eklenmesi gereken yerler olabilir. Mantık kurduktan sonra gerini halledebilirsiniz.
DECLARE @CarKod VARCHAR(15) set @CarKod=''
DECLARE @İlkTar VARCHAR(10) set @İlkTar='01-01-2014'
DECLARE @SonTar VARCHAR(10) set @SonTar='12-31-2014'
SELECT DISTINCT CC.CODE CARİ_KOD,CC.DEFINITION_ ÜNVAN
,(SELECT ROUND(SUM((1-SIGN)*TOTAL)-SUM((SIGN)*TOTAL),2) FROM LG_504_01_PAYTRANS WHERE CARDREF = CC.LOGICALREF AND PROCDATE <=@İlkTar ) AS [DEVİR]
,(SELECT SUM((SIGN)*TOTAL) FROM LG_504_01_PAYTRANS WHERE MODULENR=4 AND TRCODE=1 AND CARDREF = CC.LOGICALREF AND PROCDATE BETWEEN @İlkTar AND @SonTar ) AS [MAL ALIM]
,(SELECT SUM((SIGN)*TOTAL) FROM LG_504_01_PAYTRANS WHERE MODULENR=4 AND TRCODE=4 AND CARDREF = CC.LOGICALREF AND PROCDATE BETWEEN @İlkTar AND @SonTar ) AS [HİZMET ALIM]
,(SELECT SUM((SIGN)*TOTAL) FROM LG_504_01_PAYTRANS WHERE MODULENR=4 AND TRCODE=3 AND CARDREF = CC.LOGICALREF AND PROCDATE BETWEEN @İlkTar AND @SonTar ) AS [SATIŞ İADE]
,(SELECT SUM((1-SIGN)*TOTAL) FROM LG_504_01_PAYTRANS WHERE MODULENR=4 AND TRCODE=8 AND CARDREF = CC.LOGICALREF AND PROCDATE BETWEEN @İlkTar AND @SonTar ) AS [SATIŞ]
,(SELECT SUM((SIGN)*TOTAL) FROM LG_504_01_PAYTRANS WHERE MODULENR=10 AND TRCODE=1 AND CARDREF = CC.LOGICALREF AND PROCDATE BETWEEN @İlkTar AND @SonTar ) AS [TAHSİLAT]
,(SELECT SUM((1-SIGN)*TOTAL) FROM LG_504_01_PAYTRANS WHERE MODULENR=10 AND TRCODE=2 AND CARDREF = CC.LOGICALREF AND PROCDATE BETWEEN @İlkTar AND @SonTar ) AS [ÖDEME]
,(SELECT SUM((SIGN)*TOTAL) FROM LG_504_01_PAYTRANS WHERE MODULENR=6 AND TRCODE IN(1,2) AND CARDREF = CC.LOGICALREF AND PROCDATE BETWEEN @İlkTar AND @SonTar ) AS [ÇEK GİRİŞ]
,(SELECT SUM((1-SIGN)*TOTAL) FROM LG_504_01_PAYTRANS WHERE MODULENR=6 AND TRCODE IN(3,4) AND CARDREF = CC.LOGICALREF AND PROCDATE BETWEEN @İlkTar AND @SonTar ) AS [ÇEK ÇIKIŞ]
,(SELECT SUM((SIGN)*TOTAL) FROM LG_504_01_PAYTRANS WHERE MODULENR=7 AND TRCODE IN(3) AND CARDREF = CC.LOGICALREF AND PROCDATE BETWEEN @İlkTar AND @SonTar ) AS [GELEN HVL]
,(SELECT SUM((1-SIGN)*TOTAL) FROM LG_504_01_PAYTRANS WHERE MODULENR=7 AND TRCODE IN(4) AND CARDREF = CC.LOGICALREF AND PROCDATE BETWEEN @İlkTar AND @SonTar ) AS [GND HVL]
,(SELECT ROUND(SUM((1-SIGN)*TOTAL)-SUM((SIGN)*TOTAL),2) FROM LG_504_01_PAYTRANS WHERE CARDREF = CC.LOGICALREF ) AS [BAKİYE]
FROM LG_504_01_PAYTRANS AS PT
INNER JOIN LG_504_CLCARD AS CC ON PT.CARDREF = CC.LOGICALREF
WHERE CC.DEFINITION_ LIKE @CarKod+'%' AND PAIDINCASH=0