SQL Sorgu Malzeme Ekstresi

SELECT *,

SUM(Giriş-Çıkış) OVER (PARTITION BY [Malzeme Kodu] ORDER BY [Tarih] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) Toplam

FROM (



SELECT

STLN.DATE_ AS [Tarih],

STFIC.FICHENO AS [Fiş No],

CASE STLN.TRCODE

WHEN 1 THEN 'Mal Alım İrsaliyesi'

WHEN 2 THEN 'Perakende Satış İade İrsaliyesi'

WHEN 3 THEN 'Toptan Satış İade İrsaliyesi'

WHEN 4 THEN 'Konsinye Çıkış İade İrsaliyesi'

WHEN 5 THEN 'Konsinye Giriş İrsaliyesi'

WHEN 6 THEN 'Alım İade İrsaliyesi'

WHEN 7 THEN 'Perakende Satış İrsaliyesi'

WHEN 8 THEN 'Toptan Satış İrsaliyesi'   

WHEN 9 THEN 'Konsinye Çıkış İrsaliyesi'

WHEN 10 THEN 'Konsinye Giriş İade İrsaliyesi'

WHEN 11 THEN 'Fire Fişi'

WHEN 12 THEN 'Sarf Fişi'

WHEN 13 THEN 'Üretimden Giriş Fişi'

WHEN 14 THEN 'Devir Fişi'

WHEN 18 THEN 'Sair Giriş Fişi'

WHEN 20 THEN 'Sair Çıkış Fişi'

WHEN 25 THEN 'Ambar Fişi'

WHEN 26 THEN 'Müstahsil İrsaliyesi'

WHEN 50 THEN 'Sayım Fazlası Fişi'

WHEN 51 THEN 'Sayım Eksiği Fişi'

END AS [Fiş Türü],

ISNULL(CLC.CODE,'Firma İsmi') AS [Cari Kodu],

ISNULL(CLC.DEFINITION_,'Firma İsmi')  AS [Cari Adı],

ITM.CODE AS [Malzeme Kodu],

ITM.NAME AS [Malzeme Adı],

CASE WHEN STLN.IOCODE IN (1, 2) THEN ISNULL(STLN.AMOUNT, 0) ELSE '' END AS [Giriş],

CASE WHEN STLN.IOCODE IN (1, 2) THEN STLN.PRICE ELSE '' END AS [Giriş Fiyatı],

CASE WHEN STLN.IOCODE IN (1, 2) THEN STLN.TOTAL ELSE '' END AS [Giriş Tutarı],

CASE WHEN STLN.IOCODE IN (3, 4) THEN ISNULL(STLN.AMOUNT, 0) ELSE 0 END AS [Çıkış],

CASE WHEN STLN.IOCODE IN (3, 4) THEN STLN.PRICE ELSE 0 END AS [Çıkış Fiyatı],

CASE WHEN STLN.IOCODE IN (3, 4) THEN STLN.TOTAL ELSE 0 END AS [Çıkış Tutarı],

(SELECT SUM(ONHAND) AS Expr1 FROM LV_xxx_01_STINVTOT AS STITOTS WHERE (STOCKREF = ITM.LOGICALREF) AND (INVENNO = - 1)) AS [Fiili Stok]

FROM       

LG_xxx_01_STLINE AS STLN

LEFT OUTER JOIN LG_xxx_01_STFICHE AS STFIC ON STFIC.LOGICALREF = STLN.STFICHEREF AND ISNULL(STFIC.CANCELLED, 0) = 0

LEFT OUTER JOIN LG_xxx_CLCARD AS CLC ON CLC.LOGICALREF = STLN.CLIENTREF

LEFT OUTER JOIN LG_xxx_ITEMS AS ITM ON ITM.LOGICALREF = STLN.STOCKREF

WHERE   

(ISNULL(ITM.CODE, '') <> '') AND (STLN.AMOUNT <> 0)    AND (STLN.LINETYPE IN (0, 1, 5, 6, 8, 9, 11)) AND (STLN.STFICHEREF <> 0) AND (STFIC.GRPCODE IN (0, 1, 2, 3)) AND (STLN.TRCODE IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 18, 20, 25, 26, 50, 51)) AND (STLN.LINETYPE IN (0, 1, 5, 6, 9)) AND (STFIC.PRODSTAT IN (0)) AND (STFIC.STATUS IN (0)) OR

(ISNULL(ITM.CODE, '') <> '') AND                        (STLN.LINETYPE IN (0, 1, 5, 6, 8, 9, 11)) AND (STLN.STFICHEREF <> 0) AND (STFIC.GRPCODE IN (0, 1, 2, 3)) AND (STLN.TRCODE IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 18, 20, 25, 26, 50, 51)) AND (STLN.LINETYPE IN (0, 1, 5, 6, 9)) AND (STFIC.PRODSTAT IN (0)) AND (STFIC.STATUS IN (0)) AND (STLN.VATMATRAH <> 0) OR

(ISNULL(ITM.CODE, '') <> '') AND (STLN.AMOUNT <> 0)    AND (STLN.LINETYPE IN (0, 1, 5, 6, 8, 9, 11)) AND (STLN.STFICHEREF <> 0) AND (STFIC.GRPCODE IN (0, 1, 2, 3)) AND (STLN.TRCODE IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 18, 20, 25, 26, 50, 51)) AND (STLN.LINETYPE IN (0, 1, 5, 6, 9)) AND (STFIC.STATUS IN (0)) AND (STFIC.TRCODE IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)) OR

(ISNULL(ITM.CODE, '') <> '') AND                        (STLN.LINETYPE IN (0, 1, 5, 6, 8, 9, 11)) AND (STLN.STFICHEREF <> 0) AND (STFIC.GRPCODE IN (0, 1, 2, 3)) AND (STLN.TRCODE IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 18, 20, 25, 26, 50, 51)) AND (STLN.LINETYPE IN (0, 1, 5, 6, 9)) AND (STFIC.STATUS IN (0)) AND (STLN.VATMATRAH <> 0) AND (STFIC.TRCODE IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)) OR

(ISNULL(ITM.CODE, '') <> '') AND (STLN.AMOUNT <> 0)    AND (STLN.LINETYPE IN (0, 1, 5, 6, 8, 9, 11)) AND (STLN.STFICHEREF <> 0) AND (STFIC.GRPCODE IN (0, 1, 2, 3)) AND (STLN.TRCODE IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 18, 20, 25, 26, 50, 51)) AND (STLN.LINETYPE IN (0, 1, 5, 6, 9)) AND (STFIC.PRODSTAT IN (0)) AND (STFIC.TRCODE IN (11, 12, 13, 14, 25, 26, 50, 51)) OR

(ISNULL(ITM.CODE, '') <> '') AND                        (STLN.LINETYPE IN (0, 1, 5, 6, 8, 9, 11)) AND (STLN.STFICHEREF <> 0) AND (STFIC.GRPCODE IN (0, 1, 2, 3)) AND (STLN.TRCODE IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 18, 20, 25, 26, 50, 51)) AND (STLN.LINETYPE IN (0, 1, 5, 6, 9)) AND (STFIC.PRODSTAT IN (0)) AND (STLN.VATMATRAH <> 0) AND (STFIC.TRCODE IN (11, 12, 13, 14, 25, 26, 50, 51)) OR

(ISNULL(ITM.CODE, '') <> '') AND (STLN.AMOUNT <> 0)    AND (STLN.LINETYPE IN (0, 1, 5, 6, 8, 9, 11)) AND (STLN.STFICHEREF <> 0) AND (STFIC.GRPCODE IN (0, 1, 2, 3)) AND (STLN.TRCODE IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 18, 20, 25, 26, 50, 51)) AND (STLN.LINETYPE IN (0, 1, 5, 6, 9)) AND (STFIC.TRCODE IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)) AND (STFIC.TRCODE IN (11, 12, 13, 14, 25, 26, 50, 51)) OR

(ISNULL(ITM.CODE, '') <> '') AND                        (STLN.LINETYPE IN (0, 1, 5, 6, 8, 9, 11)) AND (STLN.STFICHEREF <> 0) AND (STFIC.GRPCODE IN (0, 1, 2, 3)) AND (STLN.TRCODE IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 18, 20, 25, 26, 50, 51)) AND (STLN.LINETYPE IN (0, 1, 5, 6, 9)) AND (STLN.VATMATRAH <> 0) AND (STFIC.TRCODE IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)) AND (STFIC.TRCODE IN (11, 12, 13, 14, 25, 26, 50, 51))

) AS TMP