SQL Sorgu Satınalma ve Satış Faturaları Raporu

Aşağıdaki SQL kodu ile satınlama ve satış raporlarını alabiliyorum ancak. ihraç kayıtlı faturaları ve ithalat faturaları ile smmm makbuzu ve kur farkı faturasını getir miyor. Bunları tabloya nasıl getirebiliriz.



SELECT
YEAR(INV.DATE_) AS 'YIL',
MONTH(INV.DATE_) AS 'AY',
INV.DATE_ AS 'FATURA TARİHİ',
INV.FICHENO AS 'FATURA NO',
CL.CODE AS 'CARİ KODU',
CL.DEFINITION_ AS 'CARİ ÜNVAN',
EMCENTER.CODE AS 'MASRAF MERKEZİ KODU',
EMCENTER.DEFINITION_ AS 'MASRAF MERKEZİ ADI',
CASE ST.LINETYPE WHEN 0 THEN IT.CODE WHEN 4 THEN SRVCARD.CODE WHEN 8 THEN IT.CODE ELSE ' ' END AS 'STOK KODU',
CASE ST.LINETYPE WHEN 0 THEN IT.NAME WHEN 4 THEN SRVCARD.DEFINITION_ WHEN 8 THEN IT.NAME ELSE ' ' END AS 'STOK ADI',
CASE ST.LINETYPE WHEN 0 THEN IT.NAME3 WHEN 4 THEN SRVCARD.DEFINITION2 WHEN 8 THEN IT.NAME3 ELSE ' ' END AS 'STOK ADI_2',
ST.AMOUNT AS 'MİKTAR',
ST.PRICE AS 'BİRİM FİYAT',
ST.VAT AS 'SATIR KDV ORANI',
ST.VATMATRAH AS 'MATRAH',
((ST.VATMATRAH*ST.VAT)/100) AS 'SATIR KDV TUTARI',
(ST.VATMATRAH+((ST.VATMATRAH*ST.VAT)/100)) AS 'SATIR GENEL TOPLAM',
CASE INV.TRCODE WHEN 1 THEN 'Satınalma Faturası' WHEN 8 THEN 'Satış Faturası' WHEN 4 THEN 'Alınan Hizmet Faturası'
WHEN 6 THEN 'Satınalma İade Faturası' WHEN 13 THEN 'Satınalma Fiyat Farkı Faturası' WHEN 3 THEN 'Satış İade Faturası'
WHEN 9 THEN 'Verilen Hizmet Faturası' WHEN 14 THEN 'Satış Fiyat Farkı' ELSE ' ' END AS 'FATURA TÜRÜ',
EMUHACC.CODE AS 'MUHASEBE HESABI KODU',
EMUHACC.DEFINITION_ AS 'MUHASEBE HESABI ADI',
CL.TAXNR AS 'VERGİ NUMARASI',
CL.TAXOFFICE AS 'VERGİ DAİRESİ',
CL.ADDR1 AS 'ADRES',
CL.TELNRS1 AS 'TELEFON NUMARASI',
CL.EMAILADDR AS 'EMAIL ADRESİ',
INV.DEPARTMENT AS 'BOLUM'

FROM LG_020_04_STLINE AS ST
LEFT OUTER JOIN LG_020_ITEMS as IT ON ST.STOCKREF=IT.LOGICALREF
LEFT OUTER JOIN LG_020_CLCARD as CL ON ST.CLIENTREF=CL.LOGICALREF
LEFT OUTER JOIN LG_020_04_INVOICE as INV ON ST.INVOICEREF=INV.LOGICALREF
LEFT OUTER JOIN LG_020_EMCENTER EMCENTER ON ST.CENTERREF=EMCENTER.LOGICALREF
LEFT OUTER JOIN LG_020_EMUHACC EMUHACC ON INV.ACCOUNTREF=EMUHACC.LOGICALREF
LEFT OUTER JOIN LG_020_SRVCARD SRVCARD ON SRVCARD.LOGICALREF=ST.STOCKREF

WHERE ((INV.TRCODE IN (1, 2, 3, 4, 12,31,8,9,6)) AND (INV.NETTOTAL <> 0) AND (INV.CLIENTREF <> 0) AND (INV.CANCELLED = 0) OR
(INV.NETTOTAL <> 0) AND (INV.CLIENTREF <> 0) AND (INV.CANCELLED = 0) AND (INV.TRCODE = 13) AND (INV.DECPRDIFF = 0) OR
(INV.NETTOTAL <> 0) AND (INV.CLIENTREF <> 0) AND (INV.CANCELLED = 0) AND (INV.TRCODE = 14) AND (INV.DECPRDIFF = 1))
AND YEAR(INV.DATE_) ='2019'

Merhaba,

Alınan ve Verilen serbest meslek makbuzları Cari Hesap fişlerine kayıt edilmektedir.
Logonun kendi sorgusunu paylaşayım.
Bs

SET ROWCOUNT 0

SELECT
CLNTC.TAXNR,CLNTC.TCKNO,CLNTC.DEFINITION_,CLNTC.LOGICALREF,SUM(XXX.LNETSUM2) AS LNETSUM, SUM(XXX.FICHECOUNT2) AS FICHECOUNT, XXX.TIG AS TIG, CLNTC.PARENTCLREF,XXX.T AS T ,XXX.INTAC AS INTAC ,XXX.TRATE AS TRATE
FROM
(SELECT
INVFC.TRCURR AS T,EXIMINFO.RESULTDATE0 AS INTAC,INVFC.TRRATE AS TRATE,INVFC.CLIENTREF,COUNT(DISTINCT INVFC.LOGICALREF) AS FICHECOUNT2, CASE TRD.GATTRIB & 1024 WHEN 1024 THEN 1 END AS TIG, SUM(CASE INVFC.TRCODE WHEN 26 THEN CASE ST.ABVATSTATUS & 2048 WHEN 2048 THEN CASE ST.ABVATSTATUS & 4096 WHEN 4096 THEN ST.TOTAL+ST.DISTEXP ELSE ST.TOTAL+ST.DISTEXP-ST.DISTDISC END ELSE CASE ST.ABVATSTATUS & 4096 WHEN 4096 THEN ST.TOTAL+ST.ADDTAXAMOUNT+ST.DISTEXP ELSE ST.TOTAL+ST.ADDTAXAMOUNT+ST.DISTEXP-ST.DISTDISC END END ELSE CASE INVFC.EXIMFCTYPE WHEN 0 THEN CASE INVFC.TRCODE WHEN 11 THEN (INVFC.NETTOTAL-INVFC.TOTALVAT) WHEN 12 THEN (INVFC.NETTOTAL-INVFC.TOTALVAT) WHEN 7 THEN CASE IT.APPSPEVATMATRAH WHEN 0 THEN ST.TOTAL/(1+(ST.VAT/100))+ST.DISTEXP ELSE CASE ST.ABVATSTATUS & 4096 WHEN 4096 THEN ST.TOTAL+ST.ADDTAXAMOUNT+ST.DISTEXP ELSE ST.TOTAL+ST.ADDTAXAMOUNT+ST.DISTEXP-ST.DISTDISC END END WHEN 2 THEN CASE IT.APPSPEVATMATRAH WHEN 0 THEN ST.TOTAL/(1+(ST.VAT/100))+ST.DISTEXP ELSE CASE ST.ABVATSTATUS & 4096 WHEN 4096 THEN ST.TOTAL+ST.ADDTAXAMOUNT+ST.DISTEXP ELSE ST.TOTAL+ST.ADDTAXAMOUNT+ST.DISTEXP-ST.DISTDISC END END WHEN 1 THEN CASE ST.ABVATSTATUS & 4096 WHEN 4096 THEN ST.TOTAL/(1+(ST.VAT/100))+ST.DISTEXP ELSE ST.TOTAL/(1+(ST.VAT/100))+ST.DISTEXP-ST.DISTDISC END ELSE CASE ISNULL(IT.APPSPEVATMATRAH, 0) WHEN 0 THEN CASE ST.VATINC WHEN 0 THEN CASE ST.ABVATSTATUS & 2048 WHEN 2048 THEN CASE ST.ABVATSTATUS & 4096 WHEN 4096 THEN ST.TOTAL+ST.DISTEXP ELSE ST.TOTAL+ST.DISTEXP-ST.DISTDISC END ELSE CASE ST.ABVATSTATUS & 4096 WHEN 4096 THEN ST.TOTAL+ST.ADDTAXAMOUNT+ST.DISTEXP ELSE ST.TOTAL+ST.ADDTAXAMOUNT+ST.DISTEXP-ST.DISTDISC END END ELSE CASE ST.ABVATSTATUS & 2048 WHEN 2048 THEN CASE ST.ABVATSTATUS & 4096 WHEN 4096 THEN (ST.TOTAL/(1+(ST.VAT/100)))+ST.DISTEXP ELSE (ST.TOTAL/(1+(ST.VAT/100)))+ST.DISTEXP-ST.DISTDISC END ELSE CASE ST.ABVATSTATUS & 4096 WHEN 4096 THEN (ST.TOTAL/(1+(ST.VAT/100)))+ST.ADDTAXAMOUNT+ST.DISTEXP ELSE (ST.TOTAL/(1+(ST.VAT/100)))+ST.ADDTAXAMOUNT+ST.DISTEXP-ST.DISTDISC END END END ELSE CASE ST.VATINC WHEN 0 THEN CASE ST.ABVATSTATUS & 2048 WHEN 2048 THEN CASE ST.ABVATSTATUS & 4096 WHEN 4096 THEN ST.TOTAL+ST.VATAMNT+ST.DISTEXP ELSE ST.TOTAL+ST.VATAMNT+ST.DISTEXP-ST.DISTDISC END ELSE CASE ST.ABVATSTATUS & 4096 WHEN 4096 THEN ST.TOTAL+ST.ADDTAXAMOUNT+ST.VATAMNT+ST.DISTEXP ELSE ST.TOTAL+ST.ADDTAXAMOUNT+ST.VATAMNT+ST.DISTEXP-ST.DISTDISC END END ELSE CASE ST.ABVATSTATUS & 2048 WHEN 2048 THEN CASE ST.ABVATSTATUS & 4096 WHEN 4096 THEN ST.TOTAL+ST.DISTEXP ELSE ST.TOTAL+ST.DISTEXP-ST.DISTDISC END ELSE CASE ST.ABVATSTATUS & 4096 WHEN 4096 THEN ST.TOTAL+ST.ADDTAXAMOUNT+ST.DISTEXP ELSE ST.TOTAL+ST.ADDTAXAMOUNT+ST.DISTEXP-ST.DISTDISC END END END END END ELSE CASE ST.ABVATSTATUS & 2048 WHEN 2048 THEN CASE ST.ABVATSTATUS & 4096 WHEN 4096 THEN ST.TOTAL+ST.DISTEXP ELSE ST.TOTAL+ST.DISTEXP-ST.DISTDISC END ELSE CASE ST.ABVATSTATUS & 4096 WHEN 4096 THEN ST.TOTAL+ST.DISTEXP+ST.ADDTAXAMOUNT ELSE ST.TOTAL+ST.DISTEXP-ST.DISTDISC+ST.ADDTAXAMOUNT END END END END ) AS LNETSUM2
FROM
LG_020_04_INVOICE INVFC WITH(NOLOCK) LEFT OUTER JOIN "GO_PLUS1"..L_TRADGRP TRD WITH(NOLOCK) ON (INVFC.TRADINGGRP = TRD.GCODE) LEFT OUTER JOIN LG_020_04_INVEXIMINFO EXIMINFO WITH(NOLOCK) ON (INVFC.LOGICALREF = EXIMINFO.INVOICEREF) LEFT OUTER JOIN LG_020_04_STLINE ST WITH(NOLOCK) ON (INVFC.LOGICALREF = ST.INVOICEREF) LEFT OUTER JOIN LG_020_ITEMS IT WITH(NOLOCK) ON (ST.STOCKREF = IT.LOGICALREF)
WHERE
(INVFC.TRCODE IN (6,7,8,9,10,11,13,14)) AND INVFC.STATUS = 0 AND (ST.LINETYPE IN (0,4,6,8,11) OR (ST.LINETYPE IS NULL)) AND (INVFC.EXIMFCTYPE IN (2,3) ) AND INVFC.NETTOTAL <> 0 AND INVFC.CLIENTREF <> 0 AND INVFC.CANCELLED = 0 AND ((INVFC.EXIMFCTYPE IN (2,3) AND EXIMINFO.RESULTDATE0 >= CONVERT(dateTime, '10-1-2019', 101) AND EXIMINFO.RESULTDATE0 <= CONVERT(dateTime, '10-31-2019', 101)) OR (INVFC.EXIMFCTYPE NOT IN (2,3) AND INVFC.DATE_ >= CONVERT(dateTime, '10-1-2019', 101) AND INVFC.DATE_ <= CONVERT(dateTime, '10-31-2019', 101)))

GROUP BY
INVFC.CLIENTREF,INVFC.TRCURR,EXIMINFO.RESULTDATE0,INVFC.TRRATE,CASE TRD.GATTRIB & 1024 WHEN 1024 THEN 1 END
UNION ALL SELECT
0 AS T, 0 AS INTAC, 0 AS TRATE, CTRNS.CLIENTREF,COUNT(DISTINCT CTRNS.LOGICALREF) AS FICHECOUNT2, CASE TRD.GATTRIB & 1024 WHEN 1024 THEN 1 END AS TIG, SUM(CASE CTRNS.TRCODE WHEN 6 THEN (CTRNS.AMOUNT*100)/(100+CTRNS.VATRATE) ELSE ROUND(CTRNS.AMOUNT / (1-CTRNS.DISCRATE/ 100 -CTRNS.DISCRATE*CTRNS.FUNDSHARERAT/10000 + (CTRNS.VATRATE - CTRNS.VATDEDUCTRATE) / 100 ),2) END) AS LNETSUM2
FROM
LG_020_04_CLFLINE CTRNS WITH(NOLOCK) LEFT OUTER JOIN LG_020_04_CLFICHE CLFIC WITH(NOLOCK) ON (CLFIC.LOGICALREF = CTRNS.SOURCEFREF) LEFT OUTER JOIN "GO_PLUS1"..L_TRADGRP TRD WITH(NOLOCK) ON (CTRNS.TRADINGGRP = TRD.GCODE)
WHERE
CTRNS.CLIENTREF <> 0 AND (CTRNS.TRCODE NOT IN (6,45) ) AND (CTRNS.TRCODE IN (6,45)) AND CTRNS.DATE_ >= CONVERT(dateTime, '1-1-2019', 101) AND (CTRNS.DATE_ <= CONVERT(dateTime, '12-31-2019', 101)) AND CTRNS.STATUS = 0

GROUP BY
CTRNS.CLIENTREF,CASE TRD.GATTRIB & 1024 WHEN 1024 THEN 1 END
) XXX LEFT OUTER JOIN LG_020_CLCARD CLNTC WITH(NOLOCK) ON (XXX.CLIENTREF = CLNTC.LOGICALREF)

GROUP BY
XXX.INTAC,XXX.T,XXX.TRATE,XXX.TIG,CLNTC.TAXNR,CLNTC.TCKNO,CLNTC.ISPERSCOMP,CLNTC.DEFINITION_,CLNTC.LOGICALREF,CLNTC.PARENTCLREF
ORDER BY
CLNTC.ISPERSCOMP,CLNTC.TCKNO,CLNTC.TAXNR



kodu bu şekilde değiştirdim ancak boş geliyor. data isimleri de aynı. niye boş getiriyor anlamadım. sondaki go kelimesini ekleyince söz dizimi hatalı diyor.

Tarih kısmını değiştirdiniz mi ?
Yada siz Beyanname bölümüne girip oradan sorgulama yapın sonrasında zaten sorgu Profiller düşecektir.

tarihi değiştirmiştim. go yakınndaki söz dizilimi yanlış diyor. go kelimesini de silince boş getiriyor. tarih ve data ismi doğru

Vedat bey yardımcı olur musunuz? Neden boş geliyor acaba?

Verileri getirttim ama sadece ihracat ile ilgili faturalar geldi. Satınalma, Satış,İhracat,İhraç Kayıtlı, Serbest Meslek Makbuzu ve Kur Farkı faturalarını aynı anda nasıl getirebiliriz.

Yardım edebilir misiniz?