• Merhaba Ziyaretçi,
    Microsoft 365 Uygulamaları ile ilgili yeni haberler, dikkat çekici konular, ilgi ile takip edeceğiniz yazılar için.

    Abone Olun
  • ESTE - Microsoft Office Eğitimleri

    Yeni yıl Microsoft Office Eğitim planlarınız için bütçenizi oluşturmadan önce ESTE eğitim kalitesi ile tanışın. 🙌
    Kullanıcıların ihtiyacı olan yazılı materyal, dosya ve video kaynağı desteğimiz ile tüm ofis çalışanlarının iş süreçlerini rahatlatacak eğitimler planlayın. 🎯
    Microsoft Office eğitimlerimiz hakkında detaylı bilgi için bize ulaşın.

    👉 Microsoft Office Eğitim Talebi

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

enverdersin

Yeni Üye
Katılım
8 Şub 2019
Mesajlar
163
En iyi yanıt
0
Puanları
18
Yaş
45
Konum
istanbul
Ad Soyad
ENVER DERSİN
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'
 

Vedat ÖZER

Logo Uzmanı
Geliştirici
Katılım
4 Haz 2018
Mesajlar
288
En iyi yanıt
4
Puanları
63
Yaş
33
Konum
Antalya / Merkez
Ad Soyad
Vedat ÖZER
Office Vers.
2019
Merhaba,

Alınan ve Verilen serbest meslek makbuzları Cari Hesap fişlerine kayıt edilmektedir.
Logonun kendi sorgusunu paylaşayım.
Bs
SQL:
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_006_01_INVOICE INVFC WITH(NOLOCK) LEFT OUTER JOIN "GODATA"..L_TRADGRP TRD WITH(NOLOCK) ON (INVFC.TRADINGGRP  =  TRD.GCODE) LEFT OUTER JOIN LG_006_01_INVEXIMINFO EXIMINFO WITH(NOLOCK) ON (INVFC.LOGICALREF  =  EXIMINFO.INVOICEREF) LEFT OUTER JOIN LG_006_01_STLINE ST WITH(NOLOCK) ON (INVFC.LOGICALREF  =  ST.INVOICEREF) LEFT OUTER JOIN LG_006_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_006_01_CLFLINE CTRNS WITH(NOLOCK) LEFT OUTER JOIN LG_006_01_CLFICHE CLFIC WITH(NOLOCK) ON (CLFIC.LOGICALREF  =  CTRNS.SOURCEFREF) LEFT OUTER JOIN "GODATA"..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, '10-1-2019', 101) AND (CTRNS.DATE_ <= CONVERT(dateTime, '10-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_006_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

go
 

enverdersin

Yeni Üye
Katılım
8 Şub 2019
Mesajlar
163
En iyi yanıt
0
Puanları
18
Yaş
45
Konum
istanbul
Ad Soyad
ENVER DERSİN
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.
 

Vedat ÖZER

Logo Uzmanı
Geliştirici
Katılım
4 Haz 2018
Mesajlar
288
En iyi yanıt
4
Puanları
63
Yaş
33
Konum
Antalya / Merkez
Ad Soyad
Vedat ÖZER
Office Vers.
2019
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.
 

enverdersin

Yeni Üye
Katılım
8 Şub 2019
Mesajlar
163
En iyi yanıt
0
Puanları
18
Yaş
45
Konum
istanbul
Ad Soyad
ENVER DERSİN
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
 

enverdersin

Yeni Üye
Katılım
8 Şub 2019
Mesajlar
163
En iyi yanıt
0
Puanları
18
Yaş
45
Konum
istanbul
Ad Soyad
ENVER DERSİN
Vedat bey yardımcı olur musunuz? Neden boş geliyor acaba?
 

enverdersin

Yeni Üye
Katılım
8 Şub 2019
Mesajlar
163
En iyi yanıt
0
Puanları
18
Yaş
45
Konum
istanbul
Ad Soyad
ENVER DERSİN
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.
 
Üst Alt