1 Konuyu açan cetinbasoz

  • cetinbasoz
  • Moderatör
  • Offline
  • Kayıt Tarihi: 07.10.2006
  • İleti: 2,303

Konu: SQL Server FIFO Update

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

Visual Fox Pro
1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

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
1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

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
1

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
1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

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

Son düzenleyen, cetinbasoz (18.12.2009 02:15:50)

Yen yaz 1907'ye yolla FB gelsin evinde yensin.
(sonra salaklar ciksin alin terine şike desin)

cetinbasoz's Website

2 Cevaplayan konuka

  • konuka
  • Üye
  • Offline
  • Yer: Antalya
  • Kayıt Tarihi: 01.01.2007
  • İleti: 586

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
  • Moderatör
  • Offline
  • Kayıt Tarihi: 07.10.2006
  • İleti: 2,303

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
1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

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;
Yen yaz 1907'ye yolla FB gelsin evinde yensin.
(sonra salaklar ciksin alin terine şike desin)

cetinbasoz's Website