Created
December 29, 2021 13:39
-
-
Save TurkerTunali/ca7c36c4c917c415f8f7a815f515bf26 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT | |
YEAR(INVOICE.DATE_) Yıl, | |
ITEMS.CODE MLZ_KODU, | |
ITEMS.NAME MLZ_ADI, | |
CASE MONTH(INVOICE.DATE_) | |
WHEN 1 THEN '01.Ocak' WHEN 2 THEN '02.Şubat' WHEN 3 THEN '03.Mart' | |
WHEN 4 THEN '04.Nisan' WHEN 5 THEN '05.Mayıs' WHEN 6 THEN '06.Haziran' | |
WHEN 7 THEN '07.Temmuz' WHEN 8 THEN '08.Ağustos' WHEN 9 THEN '09.Eylül' | |
WHEN 10 THEN '10.Ekim' WHEN 11 THEN '11.Kasım' WHEN 12 THEN '12.Aralık' ELSE '' END AS [Ay], | |
CASE INVOICE.TRCODE WHEN 7 THEN 'Perakende Satış Faturası' | |
WHEN 8 THEN 'Toptan Satış Faturası' | |
WHEN 9 THEN 'Verilen Hizmet Faturası' | |
WHEN 2 THEN 'Perakende İade Faturası' | |
WHEN 3 THEN 'Toptan İade Faturası' | |
WHEN 10 THEN 'Verilen Proforma Fatura' | |
WHEN 14 THEN 'Satınalma Fiyat Farkı Faturası' | |
ELSE '' END AS [Fatura Türü], | |
INVOICE.FICHENO AS [Fatura No], INVOICE.DATE_ AS [Tarihi], | |
CLCARD.DEFINITION_ AS [Cari Hesap Ünvanı],CLCARD.CITY Şehir, | |
CASE WHEN INVOICE.TRCODE IN(2,3) THEN SUM(STLINE.VATMATRAH +STLINE.VATAMNT )*-1 ELSE SUM(STLINE.VATMATRAH +STLINE.VATAMNT) END [Genel_Toplam], | |
CASE WHEN INVOICE.TRCODE IN(2,3) THEN SUM(AMOUNT )*-1 ELSE SUM(AMOUNT) END MIKTAR, | |
ITEMS.STGRPCODE GRUP_KODU, | |
SLSMAN.DEFINITION_ SATIS_ELEMANI | |
FROM | |
LG_021_01_STLINE STLINE INNER JOIN | |
LG_021_01_INVOICE INVOICE ON STLINE.INVOICEREF=INVOICE.LOGICALREF INNER JOIN | |
LG_021_CLCARD CLCARD ON CLCARD.LOGICALREF=INVOICE.CLIENTREF LEFT OUTER JOIN | |
LG_021_ITEMS ITEMS ON STLINE.STOCKREF = ITEMS.LOGICALREF | |
LEFT JOIN LG_SLSMAN SLSMAN ON SLSMAN.LOGICALREF=INVOICE.SALESMANREF | |
WHERE INVOICE.CANCELLED=0 AND INVOICE.TRCODE IN (2,3,7,8,9,10,14) | |
--AND SUBSTRING(ITEMS.CODE,1,8) IN ('11.00.01','11.00.02') | |
GROUP BY CLCARD.DEFINITION_ , CLCARD.CITY,INVOICE.DATE_,INVOICE.FICHENO,INVOICE.TRCODE,MONTH(INVOICE.DATE_), | |
ITEMS.STGRPCODE, | |
ITEMS.CODE , | |
ITEMS.NAME , | |
SLSMAN.DEFINITION_ |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment