/*
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
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