Merhaba Arkadaşlar,
RApor üreticisinden BA raporu ekliyoruz. Fakatsorguya YIL filtresini ekleyemedim.
SELECT AY,[SIRA NO],CARI_UNVAN,ULKE,
CASE WHEN VERGINO LIKE '120.%' THEN '1111111111' WHEN VERGINO LIKE '320.%' THEN '1111111111' ELSE VERGINO END VERGI_NO,TCK_NO,FATURA_SAYISI,TUTAR
FROM (SELECT AY,[SIRA NO],(SELECT TOP 1 DEFINITION_ FROM {CLCARD} WHERE TAXNR=TABLO.VERGINO)
CARI_UNVAN,ULKE,VERGINO,TCK_NO,SUM(FATURA_SAYISI) FATURA_SAYISI,SUM(TUTAR) TUTAR FROM
(SELECT AY=MONTH(A.DATE_),[SIRA NO]='',CARI_UNVAN=(SELECT DEFINITION_ FROM {CLCARD} WHERE LOGICALREF=CLIENTREF),
ULKE=(SELECT COUNTRY FROM {CLCARD} WHERE LOGICALREF=CLIENTREF),
VERGINO=(SELECT TAXNR FROM {CLCARD} WHERE LOGICALREF=CLIENTREF),
TCK_NO=(SELECT TCKNO FROM {CLCARD} WHERE LOGICALREF=CLIENTREF),
FATURA_SAYISI=COUNT(LOGICALREF),TUTAR=FLOOR(SUM(TOTALDISCOUNTED))
FROM {INVOICE} A WHERE
(TRCODE IN (1,4,2,3) OR (TRCODE=14 AND DECPRDIFF=1) OR (TRCODE=13 AND DECPRDIFF=0))
AND CLIENTREF<>0 AND CANCELLED=0 AND SPECODE NOT IN ('DEKONT')
GROUP BY CLIENTREF,MONTH(A.DATE_) ) TABLO
GROUP BY AY,[SIRA NO],ULKE,VERGINO,TCK_NO) TABLO2