Copy Code code as follows:
If object_id (' TB ') is not null drop table TB
If object_id (' TEMP ') is not null drop table TEMP
If object_id (' Fun_nowprice ') is not null drop FUNCTION Fun_nowprice
If object_id (' Fun_nowqty ') is not null drop FUNCTION Fun_nowqty
Go
CREATE TABLE TB (
ID INT
, Date1 datetime
, CType varchar (10)
, Qnt float
, PRI float
)
Number of--QNT
--PRI Unit Price
Insert TB
Select 0, ' 2009-1-1 ', ' incoming ', UNION ALL
Select 1, ' 2009-1-1 ', ' incoming ', +, UNION ALL
Select 2, ' 2009-1-2 ', ' Shipping ', UNION ALL
Select 3, ' 2009-1-3 ', ' incoming ', 130 union ALL
Select 4, ' 2009-1-3 ', ' shipments ', 25, 160
Go
--I want to calculate the cost price by moving the weighted average
/*
1 after purchase cost price c1= (10*100+50*120)/(10+50)
2 cost price after shipment c2= ((10+50) *C1-30*C1)/((10+50) -30) =c2
--that is, the price does not change when shipped
3 after purchase cost price c3= (((10+50) -30) *c2+40*130)/((10+50) -30+40)
-that is, when the purchase price is updated to (the total value of the current inventory + library total value)/Total quantity after storage
And so on ...
*/
Think for a long time, feel can only use loop, recursion, cursor implementation, because the price is based on the previous record.
Maybe there's a classic algorithm, who knows the trouble to teach me or send a link.
This function is the realization of recursion in disguise.
CREATE FUNCTION Fun_nowprice (@ID INT)
RETURNS NUMERIC (19,6)
As
BEGIN
Return (SELECT ISNULL (nowprice,0) from
(SELECT MAX (nowprice) ' Nowprice ' from TEMP T1 WHERE id< @ID and
Not EXISTS (SELECT 1 from TEMP WHERE id>t1.id and id< @ID))
T
End
Go
--This function is for easy calculation.
CREATE FUNCTION Fun_nowqty (@ID INT)
RETURNS NUMERIC (19,6)
As
BEGIN
Return (SELECT ISNULL SUM (case CTYPE when ' incoming ' THEN QNT ELSE 0-qnt end), 0) from TEMP WHERE id< @ID)
End
Go
--Create a temporary table containing all the fields that the original table participates in the operation
CREATE TABLE TEMP (
ID INT
, Date1 datetime
, CType varchar (10)
, Qnt float
, PRI float
, Nowprice as
Case CType
When ' Shipped ' THEN DBO. Fun_nowprice (ID)
ELSE (DBO. Fun_nowprice (ID) *dbo. Fun_nowqty (ID) +qnt*pri)/(DBO. Fun_nowqty (ID) +qnt)
End)
INSERT into TEMP
SELECT * from TB
ORDER BY DATE1 Asc,id ASC
SELECT * from TEMP
/*
0 2009-01-01 00:00:00.000 Purchase 10 100 100
1 2009-01-01 00:00:00.000 Purchase 50 120 116.666666666667
2 2009-01-02 00:00:00.000 shipments 30 150 116.666667
3 2009-01-03 00:00:00.000 Purchase 40 130 124.285714428571
4 2009-01-03 00:00:00.000 shipments 25 160 124.285714
*/
This writing is not perfect in that it is based on the ID and date of the records are sorted, for the same day out of the storage situation did not deal with. The actual application can be sorted according to CreateDate and other time landmark fields.
--------------------------------------------------------------------------------
The first time to write a technical blog, I hope this is a good start, welcome to my algorithm for correction ^_^
