SELECT
CLNTC.LOGICALREF,CLNTC.DEFINITION_,CLNTC.TAXNR,SUM(XXX.LNETSUM2) AS LNETSUM, SUM(XXX.FICHECOUNT2) AS FICHECOUNT
FROM
(SELECT
INVFC.CLIENTREF,COUNT(DISTINCT INVFC.LOGICALREF) AS FICHECOUNT2, SUM((INVFC.NETTOTAL-INVFC.TOTALVAT)) AS LNETSUM2
FROM
LG_507_01_INVOICE INVFC
WITH(NOLOCK)
WHERE
INVFC.STATUS = 0 AND (INVFC.TRCODE IN (1,2,3,4,5,12,26) OR (INVFC.TRCODE = 14 AND INVFC.DECPRDIFF = 1) OR (INVFC.TRCODE = 13 AND INVFC.DECPRDIFF = 0)) AND INVFC.NETTOTAL <> 0 AND INVFC.CLIENTREF <> 0 AND INVFC.CANCELLED = 0 AND INVFC.DATE_ >= CONVERT(dateTime, '1-1-2007', 101) AND (INVFC.DATE_ <= CONVERT(dateTime, '12-31-2007', 101))
GROUP BY
INVFC.CLIENTREF
UNION ALL SELECT
CTRNS.CLIENTREF,COUNT(DISTINCT CTRNS.LOGICALREF) AS FICHECOUNT2, SUM((CTRNS.AMOUNT*100)/(100+CTRNS.VATRATE)) AS LNETSUM2
FROM
LG_507_01_CLFLINE CTRNS
WITH(NOLOCK)
WHERE
CTRNS.CLIENTREF <> 0 AND (CTRNS.TRCODE = 6 AND CTRNS.SIGN = 1) OR (CTRNS.TRCODE IN (46)) AND CTRNS.DATE_ >= CONVERT(dateTime, '1-1-2007', 101) AND (CTRNS.DATE_ <= CONVERT(dateTime, '12-31-2007', 101))
GROUP BY
CTRNS.CLIENTREF
)XXX LEFT OUTER JOIN LG_507_CLCARD CLNTC WITH(NOLOCK) ON (XXX.CLIENTREF = CLNTC.LOGICALREF)
GROUP BY
CLNTC.DEFINITION_,CLNTC.TAXNR,CLNTC.LOGICALREF
HAVING SUM(XXX.LNETSUM2) >= 30000
ORDER BY
CLNTC.DEFINITION_
-- NOT 507 FİRMA NUMARASI DÜZELTİN. BU SORGU İLE EXCEL VERİ AKTARIMI İÇİN SQL KULLANA BİLİRSİNİZ
