Shulou(Shulou.com)05/31 Report--

This article is about how to use computed columns to implement the moving weighted average algorithm in the database. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

The code is 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)-- quantity of qnt-- pri unit price insert tb select 02009-1-1, 'purchase', 10,100 union all select 1 '2009-1-1', 'restocking', 50,120 union all select 2, 2009-1-2, shipping, 30,150 union all select 3, 2009-1-3, restocking, 40130 union all select 4, 2009-1-3, shipping, 25,160 GO-I want to calculate the cost price According to the moving weighted average / * 1, the cost price after restocking C1 = (1000050 50k120) / (105050) 2 the cost price after shipment C2 = ((105050) * C1-30*c1) / ((105050)-30) = C2-- that is, the price remains the same when the goods are shipped, and the cost price after restocking is c3 = ((105050)-30) * c2140,130) / (1050,30,40)-that is, the order at the time of purchase. The price is updated to (total value of current inventory + total value of inventory) / total quantity after storage, and so on. * /-- thought about it for a long time I think it can only be implemented with loops, recursions and cursors, because the price at the time of leaving the library is calculated according to the previous records. Maybe there is a classic algorithm, who knows, please teach me or send a link. This FUNCTION is the CREATE FUNCTION FUN_NOWPRICE (@ ID INT) RETURNS NUMERIC (19Magne6) AS BEGIN RETURN (SELECT ISNULL (NOWPRICE,0) FROM (SELECT MAX (NOWPRICE) 'NOWPRICE' FROM TEMP T1 WHERE IDT1.ID AND ID) that implements recursion in disguise.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.