Original post:
Http://community.9cbs.net/expert/topic/3236/3236660.xml?temp=.9309046
Table T1 (Recorded is the loss of product processing steps) CP_NO (product coding) CP_STEP (processing step) CP_SHL (loss rate) 001 1 0.1001 2 0.15001 3 0.2002 1 0.3002 2 0.15003 ...
Table t2 (final quantity of the product processed step) cp_no (product code) Finally_SL (final quantity) 001 5 002 7 ... Requirements: According to the final quantity provided by the T2 table and the loss rate provided by the T1 table, calculate The actual number of each processing step is obtained by T3CP_NO (product coding) CP_STEP (processing step) Real_SL (actual number) 001 1 7.35 / (1-0.1) = 8.17 001 2 6.25 / (1-0.15) = 7.35001 3 5 / ( 1-0.2) = 6.25002 1 8.24 / (1-0.3) = 11.77002 2 7 / (1-0.15) = 8.24
CP_NO is VARCHAR, CP_STEP is int, cp_shl is Numeric (18, 4), Finally_SL, Real_SL is finally_sl in Numeric (18, 4) T2 is finally obtained by all processing steps in T1, such as 001 products passing 1 The final quantity of the three steps of 2, 3 is 5. The Real_SL in T3 is obtained by Finally_SL according to the loss rate of each processing step, such as 001, by the final number 5, can obtain the actual number of step 3: 5 / (1-0.2) = 6.25, and then obtain the actual number of step 2 according to 6.25 6.25 / (1-0.15) = 7.35
-------------------------------------------------- ---------------------------------------
- Test - Test Data Create Table T1 (CP_NO VARCHAR (10), CP_STEP INT, CP_SHL NUMERIC (18, 4)) Insert T1 Select '001', 1, 0.1 ELON All Select '001', 2, 0.15Union All SELECT '001', 3, 0.2UNION All Select '002', 1, 0.3Union All Select '002', 2, 0.15 Create Table T2 (CP_NO VARCHAR (10), FINALLY_SL INT) INSERT T2 SELECT '001', 5UNION ALL SELECT '002', 7GO - Method 1, Direct Calculation (with auxiliary table) Select a.cp_no, a.cp_step, cp_shl = 1-a.cp_shl, b.finally_sl, real_sl = cast (NULL AS NUMERIC) )) INTO T3FROM T1 A, T2 bwhere a.cp_no = b.cp_noorder by a.cp_no, a.cp_step desc - calculate real_sl column declare @id varchar (10), @ SL Numeric (18, 4) Update T3 set @ SL = Case @id when cp_no kil END / CP_SE FINALLY_SL END / CP_SHL, REAL_SL = @ SL, @ ID = CP_NO - Display Procedure SELECT CP_NO, CP_STEP, REAL_SLFROM T3 Order By CP_NO, CP_STEPGO / * - Test Results CP_NO CP_STEP Real_sl ---------- --------------------- 001 1 8.17001 2 7.35001 3 6.25002 1 11.76002 2 8.24 (The number of rows affected is 5 lines) - * / - Method 2, write custom computing function, implement direct results - calculate the function of cp_shl Create F Unformation f_calc (@CP_NO VARCHAR (10), @ cp_step int, @ finally_sl int) Returns Numeric (18, 2) asbegin declare @R numeric (18, 2) set @ r = @ finally_sl select @ r = @ r / (1 -CP_SHL) from T1 WHERE CP_NO = @ CP_NO and CP_STEP> = @ cp_step Order by cp_step desc Return (@r) endgo - call function implementation query select a.cp_no, a.cp_step, real_sl = dbo.f_calc (a.cp_no , a.cp_step, b.finally_sl) from T1 A, T2 bwhere a.cp_no =
