Buda benim logo ya uyarladığım ama hata veriyor.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[_FIFO]') AND type in (N'U'))
DROP TABLE [dbo].[_FIFO]
GO
CREATE TABLE
_FIFO
(stok_kodu NVARCHAR(25),
stok_ismi NVARCHAR (50),
depodaki_miktar FLOAT,
kalan_fifo_deger FLOAT)
GO
WITH cteStockSum
AS (
SELECT STOCKREF,
SUM(CASE WHEN TRCODE = '8' THEN 0-AMOUNT
WHEN TRCODE = '1' then AMOUNT END) AS EldekiStok,PRICE AS Fiyat
FROM dbo.LG_005_01_STLINE
GROUP BY STOCKREF,PRICE
),
TerstenGirenToplami
AS (
SELECT s.STOCKREF,
s.DATE_,
(
SELECT SUM(i.AMOUNT)
FROM dbo.LG_005_01_STLINE AS i
WHERE i.STOCKREF = s.STOCKREF
AND i.TRCODE IN ('1', '1')
AND i.DATE_ >= s.DATE_
) AS YuruyenMiktar,
s.AMOUNT AS BuStok
FROM dbo.LG_005_01_STLINE AS s
WHERE s.TRCODE IN ('1', '1')
),
cteWithLastDATE_
AS (
SELECT w.STOCKREF,
w.EldekiStok,
SonKismiStok.DATE_,
SonKismiStok.StockToUse,
SonKismiStok.RunningTotal,
w.EldekiStok - SonKismiStok.RunningTotal + SonKismiStok.StockToUse AS UseBuStok
FROM cteStockSum AS w
CROSS APPLY ( SELECT TOP(1)
z.DATE_,
z.BuStok AS StockToUse,
z.YuruyenMiktar AS RunningTotal
FROM TerstenGirenToplami AS z
WHERE z.STOCKREF = w.STOCKREF
AND z.YuruyenMiktar >= w.EldekiStok
ORDER BY z.DATE_ DESC ) AS SonKismiStok
)
--
INSERT INTO _FIFO
(
stok_kodu,
stok_ismi,
depodaki_miktar,
kalan_fifo_deger
)
--
SELECT y.STOCKREF as [Stok Kodu],
dbo.fn_StokIsmi(y.STOCKREF) as [Stok Ismi],
y.EldekiStok as Depomuzdaki_Miktar ,
SUM(CASE WHEN e.DATE_ = y.DATE_ THEN y.UseBuStok
ELSE e.AMOUNT
END * Fiyat.Fiyat) AS KalanDeger
FROM cteWithLastDATE_ AS y
INNER JOIN dbo.LG_005_01_STLINE AS e
ON e.STOCKREF = y.STOCKREF
AND e.DATE_ >= y.DATE_
AND e.TRCODE IN ('1', '1')
CROSS APPLY (
SELECT TOP(1)
dbo.fn_StokHareketNetBirimFiyat(
TOTAL, --sth_tutar,
CAMPPOINTS1,--sth_iskonto1,
CAMPPOINTS2, --sth_iskonto2,
CAMPPOINTS3, --sth_iskonto3,
CAMPPOINTS4, --sth_iskonto4,
GROSSUINFO1, --sth_iskonto5,
GROSSUINFO2, --sth_iskonto6,
CMPGLINEREFS1, --sth_masraf1,
CMPGLINEREFS2, --sth_masraf2,
CMPGLINEREFS3, --sth_masraf3,
CMPGLINEREFS4, --sth_masraf4,
SPECODE, --sth_otvtutari,
SPECODE2,--sth_oivtutari,
TRCODE, --sth_tip,
0,
PRCURR, --sth_har_doviz_kuru,
PRDEXPTOTAL, --sth_alt_doviz_kuru,
PRDORDSLPLNRESERVE, --sth_stok_doviz_kuru,
nullif(AMOUNT,1)) as Fiyat
FROM dbo.LG_005_01_STLINE AS p
WHERE p.STOCKREF = e.STOCKREF
and dbo.fn_DegerFarki_mi(p.IOCODE)=0
AND p.DATE_ <= e.DATE_
AND p.TRCODE = 1
ORDER BY p.DATE_ DESC
) AS Fiyat
GROUP BY y.STOCKREF,
y.EldekiStok
ORDER BY y.STOCKREF
SELECT stok_kodu, stok_ismi, depodaki_miktar, kalan_fifo_deger
FROM _FIFO
Msg 8134, Level 16, State 1, Line 2
Divide by zero error encountered.
The statement has been terminated.
(0 row(s) affected)
yukarıdaki sorguda bir veriyi düzeltiyorum bu seferde sorgu çalışıyor ama verileri getirmiyor.
