• Merhaba Ziyaretçi,
    10 Kasım'a Kadar VIP Paket %50 İNDİRİMLİ (bir kere öde, ömür boyu kullan)
    İndirimden yararlanmak için resme tıklayın. 👇🏻
  • Sn. Ziyaretçi,
    PEAKUP E-Book & Makale & Videoları yayınlandı.

SQL Sorgu Satış ve Çek Durumu - Tahsil Portföy Dövizli

Murat OSMA

Yönetici
Site Yöneticisi
Katılım
25 May 2018
Mesajlar
1,249
En iyi yanıt
9
Puanları
113
Konum
İstanbul
Web sitesi
excelarsivi.com
Ad Soyad
Murat OSMA
Logo'da Satış ve Çek durum tablosu için bu sorguyu kullanabilirsiniz.

[HIDEREPLYTHANKS]
SQL:
SELECT C.CODE CARIKODU, C.DEFINITION_ CARIUNVANI,
    ROUND(ISNULL((select SUM(TOTAL)-SUM(PAID) from LG_500_01_PAYTRANS WHERE CARDREF=C.LOGICALREF),0),2)BAKIYE,
    ROUND(ISNULL((SELECT SUM(TRNET) FROM lg_500_01_INVOICE WHERE TRCURR NOT IN(0,160) AND CLIENTREF=C.LOGICALREF AND CANCELLED=0 AND
    YEAR(DATE_)=YEAR(GETDATE()) AND MONTH(DATE_)=1 AND GRPCODE=2),0),2)OCA,
    ROUND(ISNULL((SELECT SUM(TRNET) FROM lg_500_01_INVOICE WHERE TRCURR NOT IN(0,160) AND CLIENTREF=C.LOGICALREF AND CANCELLED=0 AND
    YEAR(DATE_)=YEAR(GETDATE()) AND MONTH(DATE_)=2 AND GRPCODE=2),0),2)SUB,
    ROUND(ISNULL((SELECT SUM(TRNET) FROM lg_500_01_INVOICE WHERE TRCURR NOT IN(0,160) AND CLIENTREF=C.LOGICALREF AND CANCELLED=0 AND
    YEAR(DATE_)=YEAR(GETDATE()) AND MONTH(DATE_)=3 AND GRPCODE=2),0),2)MAR,
    ROUND(ISNULL((SELECT SUM(TRNET) FROM lg_500_01_INVOICE WHERE TRCURR NOT IN(0,160) AND CLIENTREF=C.LOGICALREF AND CANCELLED=0 AND
    YEAR(DATE_)=YEAR(GETDATE()) AND MONTH(DATE_)=4 AND GRPCODE=2),0),2)NIS,
    ROUND(ISNULL((SELECT SUM(TRNET) FROM lg_500_01_INVOICE WHERE TRCURR NOT IN(0,160) AND CLIENTREF=C.LOGICALREF AND CANCELLED=0 AND
    YEAR(DATE_)=YEAR(GETDATE()) AND MONTH(DATE_)=5 AND GRPCODE=2),0),2)MAY,
    ROUND(ISNULL((SELECT SUM(TRNET) FROM lg_500_01_INVOICE WHERE TRCURR NOT IN(0,160) AND CLIENTREF=C.LOGICALREF AND CANCELLED=0 AND
    YEAR(DATE_)=YEAR(GETDATE()) AND MONTH(DATE_)=6 AND GRPCODE=2),0),2)HAZ,
    ROUND(ISNULL((SELECT SUM(TRNET) FROM lg_500_01_INVOICE WHERE TRCURR NOT IN(0,160) AND CLIENTREF=C.LOGICALREF AND CANCELLED=0 AND
    YEAR(DATE_)=YEAR(GETDATE()) AND MONTH(DATE_)=7 AND GRPCODE=2),0),2)TEM,
    ROUND(ISNULL((SELECT SUM(TRNET) FROM lg_500_01_INVOICE WHERE TRCURR NOT IN(0,160) AND CLIENTREF=C.LOGICALREF AND CANCELLED=0 AND
    YEAR(DATE_)=YEAR(GETDATE()) AND MONTH(DATE_)=8 AND GRPCODE=2),0),2)AGU,
    ROUND(ISNULL((SELECT SUM(TRNET) FROM lg_500_01_INVOICE WHERE TRCURR NOT IN(0,160) AND CLIENTREF=C.LOGICALREF AND CANCELLED=0 AND
    YEAR(DATE_)=YEAR(GETDATE()) AND MONTH(DATE_)=9 AND GRPCODE=2),0),2)EYL,
    ROUND(ISNULL((SELECT SUM(TRNET) FROM lg_500_01_INVOICE WHERE TRCURR NOT IN(0,160) AND CLIENTREF=C.LOGICALREF AND CANCELLED=0 AND
    YEAR(DATE_)=YEAR(GETDATE()) AND MONTH(DATE_)=10 AND GRPCODE=2),0),2)EKI,
    ROUND(ISNULL((SELECT SUM(TRNET) FROM lg_500_01_INVOICE WHERE TRCURR NOT IN(0,160) AND CLIENTREF=C.LOGICALREF AND CANCELLED=0 AND
    YEAR(DATE_)=YEAR(GETDATE()) AND MONTH(DATE_)=11 AND GRPCODE=2),0),2)KAS,
    ROUND(ISNULL((SELECT SUM(TRNET) FROM lg_500_01_INVOICE WHERE TRCURR NOT IN(0,160) AND CLIENTREF=C.LOGICALREF AND CANCELLED=0 AND
    YEAR(DATE_)=YEAR(GETDATE()) AND MONTH(DATE_)=12 AND GRPCODE=2),0),2)ARA,
    ROUND(ISNULL((SELECT SUM(AMOUNT) FROM lg_500_01_CSCARD WHERE LOGICALREF IN
    (SELECT CSREF FROM lg_500_01_CSTRANS WHERE TRCODE=1 AND STATNO=1 AND CARDREF=C.LOGICALREF)),0),2)PORTFOYCEKI,
    ROUND(ISNULL((SELECT SUM(TOTAL) FROM lg_500_01_CSROLL WHERE LOGICALREF IN(
    SELECT ROLLREF FROM (SELECT * FROM lg_500_01_CSTRANS WHERE CSREF IN(
    SELECT CSREF FROM lg_500_01_CSTRANS WHERE TRCODE=5 AND STATNO=1))A
WHERE STATNO<>1 AND CARDREF=C.LOGICALREF)),0),2) TAHSILCEKI
FROM lg_500_CLCARD C ORDER BY CODE
[/HIDEREPLYTHANKS]
 
Son düzenleme:

jonest

Yeni Üye
Katılım
13 Ocak 2020
Mesajlar
9
En iyi yanıt
0
Puanları
1
Yaş
28
Konum
Mardin
Ad Soyad
Mehmet Beşir PARLAKOĞLU
eyvallah Teşekkür Ederim
 
Üst Alt