CREATE VIEW dbo.CARI_MALZEME_ALIM_TOPLAM_TUTAR
AS
SELECT        CC.CODE AS [FIRMA KODU], CC.DEFINITION_ AS [FIRMA ÜNVANI], CC.CITY AS SEHIR, CC.DISCRATE AS ISKONTO, CASE WHEN ISNULL(CAST(XT.FIS2 AS VARCHAR), '') <> '' AND ISNULL(CAST(XT.FIS2 AS VARCHAR), '') 
                         <> '0' THEN CONCAT(CONCAT(ISNULL(CAST(XT.FIS AS VARCHAR), ''), '+'), ISNULL(CAST(XT.FIS2 AS VARCHAR), '')) ELSE CASE WHEN ISNULL(CAST(XT.FIS AS VARCHAR), '') = '0' THEN '' ELSE ISNULL(CAST(XT.FIS AS VARCHAR), 
                         '') END END AS [FIS ISKONTOSU], CASE WHEN ISNULL(CAST(XT.PESIN2 AS VARCHAR), '') <> '' AND ISNULL(CAST(XT.PESIN2 AS VARCHAR), '') <> '0' THEN CONCAT(CONCAT(ISNULL(CAST(XT.PESIN AS VARCHAR), ''), '+'), 
                         ISNULL(CAST(XT.PESIN2 AS VARCHAR), '')) ELSE CASE WHEN ISNULL(CAST(XT.PESIN AS VARCHAR), '') = '0' THEN '' ELSE ISNULL(CAST(XT.PESIN AS VARCHAR), '') END END AS [PESIN ISKONTOSU], 
                         ITM.CODE AS [MALZEME KODU], ITM.NAME AS [MALZEME AÇIKLAMASI], ITM.SPECODE3 AS [MALZEME GRUBU], SUM(STL.VATAMNT) AS KDV, SUM(STL.LINENET) AS NET, SUM(STL.LINENET + STL.VATAMNT) 
                         AS [TOPLAM SATIS], SUM(STL.AMOUNT) AS MIKTAR, COUNT(DISTINCT STL.INVOICEREF) AS [FATURA ADEDI], CC.SPECODE2
FROM            dbo.LG_011_07_STLINE AS STL LEFT OUTER JOIN
                         dbo.LG_011_CLCARD AS CC ON CC.LOGICALREF = STL.CLIENTREF LEFT OUTER JOIN
                         dbo.LG_XT1015_011 AS XT ON XT.PARLOGREF = STL.CLIENTREF LEFT OUTER JOIN
                         dbo.LG_011_ITEMS AS ITM ON ITM.LOGICALREF = STL.STOCKREF
WHERE        (STL.TRCODE = 8) AND (STL.CANCELLED = 0) AND (STL.LINETYPE = 0) AND (STL.INVOICEREF <> 0)
GROUP BY CC.CODE, CC.DEFINITION_, CC.CITY, ITM.CODE, ITM.NAME, ITM.SPECODE3, CC.DISCRATE, ISNULL(CAST(XT.FIS AS VARCHAR), ''), ISNULL(CAST(XT.FIS2 AS VARCHAR), ''), ISNULL(CAST(XT.PESIN AS VARCHAR), ''), 
                         ISNULL(CAST(XT.PESIN2 AS VARCHAR), ''), CC.SPECODE2