LOGO_SATIS.sql

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_