Include key points: Database loop, insert select sentence, random number (rand () function ), absolute Value (abs () function )
DECLARE @starttime varchar( -) DECLARE @endtime varchar( -) DECLARE @randomvalue float SET @starttime='2017-09-15 00:15:00.000' SET @endtime='2017-10-20 00:00:00.000'--set @randomvalue =floor (rand () *10)----Select CONVERT (varchar), DATEADD (mi,15, @starttime), 121)--SELECT [Tagid],[value][email protected],[startvalue][email protected],convert (varchar), DATEADD (mi,15,[ StartTime]), 121), [Endvalue][email protected],convert (varchar (+), DATEADD (Mi,15,[endtime]), 121)--From [dbo]. [Dimtagminutedata] where TagID in (1,--2,3,4,5,6,--7,8,9,10,11,--12,13,14,15,16,--17,18,19,20-- )--and [email protected] and endtime=convert (varchar (), DATEADD (mi,15, @starttime), 121) while @starttime<=@endtime begin Set @randomvalue= Floor(Rand()*Ten) IF @starttime<=@endtime BEGIN Insert intoDimtagminutedata ([TagID],[Value],[Startvalue] ,[StartTime] ,[Endvalue] ,[EndTime]) Select [TagID] , ABS([Value]-@randomvalue) , ABS([Startvalue]-@randomvalue), CONVERT(varchar( -),DATEADD(MI, the,[StartTime]),121), ABS([Endvalue]-@randomvalue), CONVERT(varchar( -),DATEADD(MI, the,[EndTime]),121) from [dbo].[Dimtagminutedata] whereTagIDinch(1, 2,3,4,5,6, 7,8,9,Ten, One, A, -, -, the, -, -, -, +, - ) andStartTime=@starttime andEndTime=CONVERT(varchar( -),DATEADD(MI, the,@starttime),121) End Else begin Print 'Stop' End Set @starttime= CONVERT(varchar( -),DATEADD(MI, the,@starttime),121) End --Random number Select Floor(Rand()* -)
Of course, the loop can be judged without the if else.
SQL Server 2008 Creating test data