- Katılım
- 25 May 2018
- Mesajlar
- 1,609
- En iyi yanıt
- 14
- Puanları
- 113
- Konum
- İstanbul
- Web sitesi
- excelarsivi.com
- Ad Soyad
- Murat OSMA
- Office Vers.
- Microsoft 365 TR+EN
Logo'da Satış ve Çek durum tablosu için bu sorguyu kullanabilirsiniz.
[HIDEREPLYTHANKS]
[/HIDEREPLYTHANKS]
[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
Son düzenleme: