/*==============================================================
創建日期:2011-9-22
作 者:Yu
1.先取得EC003 中各基金的最大日期那筆資料放入temp表;
2.修改guid ,並且更新 temp 表中各列的日期 = 日期+1天,修改淨值=淨值* (1+rand(-0.09到 0.09);
3.刪除掉temp表中經過2修正的日期 是大於今天的;
4.將temp表中資料寫回EC003
==============================================================*/
ALTER PROCEDURE PR_EC003_AutoInsertData
AS
BEGIN
--Declare rand range
DECLARE @MinValue DECIMAL(18,2) SET @MinValue=-0.09;
DECLARE @MaxValue DECIMAL(18,2) SET @MaxValue=0.09;
--Step 1
SELECT * INTO Temp FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY EC00306 ORDER BY EC00310 DESC) rn,* FROM EC003
) t WHERE rn < 2;
--test
--SELECT * FROM Temp;
--Step 2
UPDATE Temp
SET
EC00301=NEWID(),
EC00310=DATEADD(DAY, 1, EC00310),
EC00309=EC00309*(1+RAND()*(@MaxValue-@MinValue)+@MinValue);
--test
--SELECT * FROM Temp;
--Step 3
DELETE FROM Temp WHERE EC00310>GETDATE();
--Step 4
INSERT INTO EC003
SELECT
EC00301,
EC00306,
EC00307,
EC00308,
EC00309,
EC00310,
EC00311,
EC00312,
EC00313,
EC00314,
EC00315,
EC00316
FROM Temp;
DROP TABLE Temp;
END;
GO
創建日期:2011-9-22
作 者:Yu
1.先取得EC003 中各基金的最大日期那筆資料放入temp表;
2.修改guid ,並且更新 temp 表中各列的日期 = 日期+1天,修改淨值=淨值* (1+rand(-0.09到 0.09);
3.刪除掉temp表中經過2修正的日期 是大於今天的;
4.將temp表中資料寫回EC003
==============================================================*/
ALTER PROCEDURE PR_EC003_AutoInsertData
AS
BEGIN
--Declare rand range
DECLARE @MinValue DECIMAL(18,2) SET @MinValue=-0.09;
DECLARE @MaxValue DECIMAL(18,2) SET @MaxValue=0.09;
--Step 1
SELECT * INTO Temp FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY EC00306 ORDER BY EC00310 DESC) rn,* FROM EC003
) t WHERE rn < 2;
--test
--SELECT * FROM Temp;
--Step 2
UPDATE Temp
SET
EC00301=NEWID(),
EC00310=DATEADD(DAY, 1, EC00310),
EC00309=EC00309*(1+RAND()*(@MaxValue-@MinValue)+@MinValue);
--test
--SELECT * FROM Temp;
--Step 3
DELETE FROM Temp WHERE EC00310>GETDATE();
--Step 4
INSERT INTO EC003
SELECT
EC00301,
EC00306,
EC00307,
EC00308,
EC00309,
EC00310,
EC00311,
EC00312,
EC00313,
EC00314,
EC00315,
EC00316
FROM Temp;
DROP TABLE Temp;
END;
GO