history_procedure

/*


please change the @MspID_real to the  your real msp ID 


*/


Create proc proc_AddHistory(
@MspID int,
@MspID_real int,
@BoWID_pool int,
@BoWID_spa int,
@AttributeID_WaterTemp int,
@AttributeID_AirTemp int,
@AttributeID_ph int,
@AttributeID_ORP int,
@CSAD_ObjectID_Pool int,
@CSAD_ObjectID_spa int
)
as
print 'Insert into MSP:'+cast(@MspID_real as varchar)
print @MspID 
print @BoWID_spa
print @BoWID_pool
print @AttributeID_ph
print @AttributeID_ORP
print @CSAD_ObjectID_Pool 
print @CSAD_ObjectID_spa 






insert into StatusHistory ([AttributeID]
      ,[ObjectID]
      ,[Value]
      ,[EntryTime]
      ,[MspID]
      )
      select 
 @AttributeID_AirTemp,@MspID, cast(ceiling(RAND()*100)+20 as varchar),DATEADD(MI,+5,DATEADD(DAY,-1,GETDATE())),@MspID
 UNION select 
 @AttributeID_AirTemp,@MspID, cast(ceiling(RAND()*100)+20 as varchar),DATEADD(MI,+15,DATEADD(wk,-1,GETDATE())),@MspID
 UNION select 
  @AttributeID_AirTemp,@MspID, cast(ceiling(RAND()*100)+20 as varchar),DATEADD(MI,+5,DATEADD(wk,-1,GETDATE())),@MspID
 UNION select 
  @AttributeID_AirTemp,@MspID, cast(ceiling(RAND()*100)+20 as varchar),DATEADD(HH,+1, DATEADD(mm,-1,getDate())),@MspID
 UNION select 
    @AttributeID_AirTemp,@MspID, cast(ceiling(RAND()*100)+20 as varchar),DATEADD(MI,+15, DATEADD(mm,-1,getDate())),@MspID



UNION select 
 @AttributeID_ORP,@CSAD_ObjectID_Pool, cast(ceiling(RAND()*1060) as varchar),DATEADD(MI,+5,DATEADD(DAY,-1,GETDATE())),@MspID,@BoWID_pool
 UNION select 
 @AttributeID_ORP,@CSAD_ObjectID_Pool, cast(ceiling(RAND()*1060) as varchar),DATEADD(MI,+15,DATEADD(wk,-1,GETDATE())),@MspID,@BoWID_pool
 UNION select 
  @AttributeID_ORP,@CSAD_ObjectID_Pool, cast(ceiling(RAND()*1060)as varchar),DATEADD(MI,+5,DATEADD(wk,-1,GETDATE())),@MspID,@BoWID_pool
 UNION select 
  @AttributeID_ORP,@CSAD_ObjectID_Pool, cast(ceiling(RAND()*1060) as varchar),DATEADD(HH,+1, DATEADD(mm,-1,getDate())),@MspID,@BoWID_pool
 UNION select 
    @AttributeID_ORP,@CSAD_ObjectID_Pool, cast(ceiling(RAND()*1060) as varchar),DATEADD(MI,+15, DATEADD(mm,-1,getDate())),@MspID,@BoWID_pool


  UNION select 
  @AttributeID_ORP,@CSAD_ObjectID_spa, cast(ceiling(RAND()*1060) as varchar),DATEADD(MI,+5,DATEADD(DAY,-1,GETDATE())),@MspID,@BoWID_spa
 UNION select 
 @AttributeID_ORP,@CSAD_ObjectID_spa, cast(ceiling(RAND()*1060) as varchar),DATEADD(MI,+15,DATEADD(wk,-1,GETDATE())),@MspID,@BoWID_spa
 UNION select 
  @AttributeID_ORP,@CSAD_ObjectID_spa, cast(ceiling(RAND()*1060) as varchar),DATEADD(MI,+5,DATEADD(wk,-1,GETDATE())),@MspID,@BoWID_spa
 UNION select 
  @AttributeID_ORP,@CSAD_ObjectID_spa, cast(ceiling(RAND()*1060)as varchar),DATEADD(HH,+1, DATEADD(mm,-1,getDate())),@MspID,@BoWID_spa
 UNION select 
    @AttributeID_ORP,@CSAD_ObjectID_spa, cast(ceiling(RAND()*1060) as varchar),DATEADD(MI,+15, DATEADD(mm,-1,getDate())),@MspID,@BoWID_spa


  
  






 insert into StatusHistory ([AttributeID]
      ,[ObjectID]
      ,[Value]
      ,[EntryTime]
      ,[MspID]
      ,[BoWID] )
  select 
      @AttributeID_WaterTemp,@BoWID_pool, cast(ceiling(RAND()*100)+20 as varchar),DATEADD(MI,+5,DATEADD(DAY,-1,GETDATE())),@MspID,@BoWID_pool
 UNION select 
 @AttributeID_WaterTemp,@BoWID_pool, cast(ceiling(RAND()*100)+20 as varchar),DATEADD(MI,+15,DATEADD(wk,-1,GETDATE())),@MspID,@BoWID_pool
 UNION select 
  @AttributeID_WaterTemp,@BoWID_pool, cast(ceiling(RAND()*100)+20 as varchar),DATEADD(MI,+5,DATEADD(wk,-1,GETDATE())),@MspID,@BoWID_pool
 UNION select 
  @AttributeID_WaterTemp,@BoWID_pool, cast(ceiling(RAND()*100)+20 as varchar),DATEADD(HH,+1, DATEADD(mm,-1,getDate())),@MspID,@BoWID_pool
 UNION select 
    @AttributeID_WaterTemp,@BoWID_pool, cast(ceiling(RAND()*100)+20 as varchar),DATEADD(MI,+15, DATEADD(mm,-1,getDate())),@MspID,@BoWID_pool
  UNION select    
  @AttributeID_WaterTemp,@BoWID_spa, cast(ceiling(RAND()*100)+20 as varchar),DATEADD(MI,+5,DATEADD(DAY,-1,GETDATE())),@MspID,@BoWID_spa
 UNION select 
 @AttributeID_WaterTemp,@BoWID_spa, cast(ceiling(RAND()*100)+20 as varchar),DATEADD(MI,+15,DATEADD(wk,-1,GETDATE())),@MspID,@BoWID_spa
 UNION select 
  @AttributeID_WaterTemp,@BoWID_spa, cast(ceiling(RAND()*100)+20 as varchar),DATEADD(MI,+5,DATEADD(wk,-1,GETDATE())),@MspID,@BoWID_spa
 UNION select 
  @AttributeID_WaterTemp,@BoWID_spa, cast(ceiling(RAND()*100)+20 as varchar),DATEADD(HH,+1, DATEADD(mm,-1,getDate())),@MspID,@BoWID_spa
 UNION select 
    @AttributeID_WaterTemp,@BoWID_spa, cast(ceiling(RAND()*100)+20 as varchar),DATEADD(MI,+15, DATEADD(mm,-1,getDate())),@MspID,@BoWID_spa
 
 GO


Declare @MspID int,
@MspID_real int,
@BoWID_pool int,
@BoWID_spa int,
@AttributeID_WaterTemp int,
@AttributeID_AirTemp int,
@AttributeID_ph int,
@AttributeID_ORP int,
@CSAD_ObjectID_Pool int,
@CSAD_ObjectID_spa int;


set @MspID_real=25;
set @MspID=(select ID FROM Msp where SystemID=@MspID_real);
set @BoWID_pool=(select ID from BoW where MspID=@MspID and Type='0');
set @BoWID_spa=(select ID from BoW where MspID=@MspID and Type='1');
set @AttributeID_WaterTemp=(Select ID from StatusAttribute where Name='WaterTemp');
set @AttributeID_AirTemp=(Select ID from StatusAttribute where Name='AirTemp');
set @AttributeID_ph=(select ID from StatusAttribute where Name='PH' and ObjectType='CSAD');
set @AttributeID_ORP=(select ID from StatusAttribute where Name='ORP' and ObjectType='CSAD');
set @CSAD_ObjectID_Pool=(select ID FROM CSAD where MspID=@MspID and BoWID=@BoWID_pool);
set @CSAD_ObjectID_spa=(select ID FROM CSAD where MspID=@MspID and BoWID=@BoWID_spa);




exec proc_AddHistory @MspID,@MspID_real,@BoWID_pool,
@BoWID_spa,@AttributeID_WaterTemp,@AttributeID_AirTemp,
@AttributeID_ph,@AttributeID_ORP,
@CSAD_ObjectID_Pool ,@CSAD_ObjectID_spa