1 Konuyu açan cetinbasoz Son düzenleyen, cetinbasoz (18.12.2009 02:15:50)

  • cetinbasoz
  • cetinbasoz
  • Uzman Üye
  • Offline
  • Kayıt Tarihi: 07.10.2006
  • İleti: 3,162

Konu: SQL Server FIFO Update

Sonunda verdigim sozu tutup SQL server'da FIFO update kodunu yazabildim:)
Once ornek data:

Visual Fox Pro
CREATE TABLE stock

    (

      id INT IDENTITY

             PRIMARY KEY,

      itemId INT,

      inDate DATETIME default getDate(),

      Qty INT

    ) ;

 

INSERT  INTO stock ( itemId, inDate, Qty ) VALUES  ( 1, '2009/1/1', 1000 ) ;

INSERT  INTO stock ( itemId, inDate, Qty ) VALUES  ( 1, '2009/1/2', 2000 ) ;

INSERT  INTO stock ( itemId, inDate, Qty ) VALUES  ( 1, '2009/1/3', 3000 ) ;

INSERT  INTO stock ( itemId, inDate, Qty ) VALUES  ( 2, '2009/1/1', 200 ) ;

INSERT  INTO stock ( itemId, inDate, Qty ) VALUES  ( 2, '2009/1/2', 100 ) ;

INSERT  INTO stock ( itemId, inDate, Qty ) VALUES  ( 2, '2009/1/3', 300 ) ;

INSERT  INTO stock ( itemId, inDate, Qty ) VALUES  ( 2, '2009/1/4', 100 ) ;

INSERT  INTO stock ( itemId, inDate, Qty ) VALUES  ( 2, '2009/1/5', 100 ) ;

INSERT  INTO stock ( itemId, inDate, Qty ) VALUES  ( 2, '2008/12/1', 100 ) ;

FIFO Update:

Visual Fox Pro
declare @itemId int, @qty int;

set @itemID = 2;

set @qty = 360;

 

UPDATE Stock

SET Qty = CASE

  WHEN @qty >= 

   (SELECT sum(qty)

   FROM stock stk 

   WHERE itemId=@itemID and stk.inDate <= Stock.inDate)

  THEN 0

  WHEN @qty <

   (SELECT sum(qty)-Stock.qty 

   FROM stock stk 

   WHERE itemId=@itemID and stk.inDate <= Stock.inDate)

  THEN Stock.Qty 

  ELSE

   (SELECT sum(qty) 

   FROM stock stk 

   WHERE itemId=@itemID and stk.inDate <= Stock.inDate) - @qty

  END

where itemId = @itemId;

 

select * from stock;

Not: Yukaridaki sekliyle aslinda SP yapilmaya uygun halde:

Visual Fox Pro
create procedure FIFOUpdate( @itemId int, @qty int ) As ...

SP olarak yazmamamin nedeni, bu haliyle hic SP yapmadan parametrik tek bir update komutu olarak ta VFP'den kullanilabilir:

Visual Fox Pro
text to lcUpdate noshow

UPDATE Stock

SET Qty = CASE

  WHEN ?m.qty >= 

   (SELECT sum(qty)

   FROM stock stk 

   WHERE itemId=?m.itemID and stk.inDate <= Stock.inDate)

  THEN 0

  WHEN ?m.qty <

   (SELECT sum(qty)-Stock.qty 

   FROM stock stk 

   WHERE itemId=?m.itemID and stk.inDate <= Stock.inDate)

  THEN Stock.Qty 

  ELSE

   (SELECT sum(qty) 

   FROM stock stk 

   WHERE itemId=?m.itemID and stk.inDate <= Stock.inDate) - ?m.qty

  END

where itemId = ?m.itemId;

endtext

 

qty = 360

itemId = 2

SQLExec(m.handle, m.lcUpdate)

Not: Joe Celko'nun FIFO/LIFO makalesinden yararlanilmistir.

http://www.dbazine.com/ofinterest/oi-articles/celko32

cetinbasoz's Website

2 Cevaplayan konuka

  • konuka
  • konuka
  • Uzman Üye
  • Offline
  • Yer: İstanbul
  • Kayıt Tarihi: 01.01.2007
  • İleti: 1,832

Re: SQL Server FIFO Update

Sevgili Çetin,
özellikle çok teşekkürler. Halen konsantrasyonum başka projede, toparlar toparlamaz uygulayıp bilgi vereceğim.
İlk bakışta (SQLde CASE uygulaması ile yakın zamanda tanıştım) çalışmasını bekliyorum.

VFP9 SP2

konuka's Website

3 Cevaplayan cetinbasoz

  • cetinbasoz
  • cetinbasoz
  • Uzman Üye
  • Offline
  • Kayıt Tarihi: 07.10.2006
  • İleti: 3,162

Re: SQL Server FIFO Update

O kodu oncelikle cozum olsun diye yazmistim ve tesadufen benzeri soru baska yerde olunca oraya link verdim. Sonucta atlayip efektif degil diyen cikti tabii:) O nedenle daha efektif CTE versiyonu:

Visual Fox Pro
declare @itemId int, @qty int;

set @itemID = 2;

set @qty = 360;

 

WITH stk (inDate, totQty) AS

( 

SELECT t1.inDate, sum(t2.qty)

   FROM stock t1

   INNER JOIN stock t2

   ON t1.itemId = t2.itemID AND t2.inDate <= t1.inDate 

   WHERE t1.itemId=@itemID

   GROUP BY t1.inDate

)

UPDATE Stock

SET Qty = CASE

  WHEN @qty >= 

   (SELECT totQty

   FROM stk 

   WHERE stk.inDate = Stock.inDate)

  THEN 0

  WHEN @qty <

   (SELECT totQty-Stock.qty 

   FROM stk 

   WHERE stk.inDate = Stock.inDate)

  THEN Stock.Qty 

  ELSE

   (SELECT totQty 

   FROM stk 

   WHERE stk.inDate = Stock.inDate) - @qty

  END

where itemId = @itemId;

 

select * from stock;

cetinbasoz's Website