USE [cheminova] GO /****** Object: StoredProcedure [dbo].[SP_Firstdistil] Script Date: 10/30/2014 11:55:31 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Pragya> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= ALTER PROCEDURE [dbo].[SP_Firstdistil] -- Add the parameters for the stored procedure here -- @StartDate varchar(50)=Null, --@EndDate varchar(50)=Null @SDate DateTime AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here declare @StartDate varchar(50) declare @EndDate varchar(50) set @EndDate=CONVERT(varchar,DATEPART(month,@SDate))+'/'+CONVERT(varchar,DATEPART(DAY,@SDate))+'/'+CONVERT(varchar,DATEPART(YEAR,@SDate))+' 06:00:00' set @StartDate=CONVERT(varchar,DATEPART(month,DATEADD(day,-1,@SDate)))+'/'+CONVERT(varchar,DATEPART(DAY,DATEADD(day,-1,@SDate)))+'/'+CONVERT(varchar,DATEPART(YEAR,DATEADD(day,-1,@SDate)))+' 06:00:00' Begin --table to store data of each tag for a day create table #tempval ( [ID] [int] IDENTITY(1,1) NOT NULL, tagname varchar(100), [timestamp] Varchar(50), tagval varchar(50), quality varchar(20) ) create table #tempval1 ( [ID] [int] IDENTITY(1,1) NOT NULL, tagname varchar(100), [timestamp] Varchar(50), tagval varchar(50), quality varchar(20) ) create table #tempval2 ( [ID] [int] IDENTITY(1,1) NOT NULL, tagname varchar(100), [timestamp] Varchar(50), tagval varchar(50), quality varchar(20) ) create table #tempval3 ( [ID] [int] IDENTITY(1,1) NOT NULL, tagname varchar(100), [timestamp] Varchar(50), tagval varchar(50), quality varchar(20) ) --table to store average of each tag for a day create table #tagavg ( [ID] [int] IDENTITY(1,1) NOT NULL, avgtag float ) create table #temp1 ( [ID] [int] IDENTITY(1,1) NOT NULL, --[Timestamp] varchar(50), [Value][float] NULL ) create table #temp2 ( [ID] [int] IDENTITY(1,1) NOT NULL, --[Timestamp] varchar(50), [Value][float] NULL ) create table #temp3 ( [ID] [int] IDENTITY(1,1) NOT NULL, --[Timestamp] varchar(50), [Value][float] NULL ) create table #temp4 ( [ID] [int] IDENTITY(1,1) NOT NULL, --[Timestamp] varchar(50), [Value][float] NULL ) create table #temp5 ( [ID] [int] IDENTITY(1,1) NOT NULL, --[Timestamp] Datetime, [Value][float] NULL ) create table #tempdigival ( [ID] [int] IDENTITY(1,1) NOT NULL, [timestamp] varchar(30), tagval varchar(30) ) create table #tempsum ( tagval varchar(30), calevent float ) create table #tagtemp ( [ID] [int] IDENTITY(1,1) NOT NULL, [timestamp] datetime, tagname varchar(200), descrip varchar(200), unit varchar(10), tagval varchar(38), calevent float ) create table #totalizervalue ( [ID] [int] IDENTITY(1,1) NOT NULL, tagname varchar(200), waqt varchar(30), tagval varchar(30) ) create table #FinalResult ( [ID] [int] IDENTITY(1,1) NOT NULL, tag1 float, tag2 float, tag3 float, tag4 float, tag5 float, tag6 float ) --to store reading --select @StartDate as startdate,@EndDate as enddate DECLARE @SQL as varchar(1000) Declare @Tagname varchar(300) Declare @tagVal float Declare @calval float declare @avgtag float declare @X1 float declare @Y1 float declare @Z1 float declare @X2 float declare @Y2 float declare @Z2 float declare @X3 float declare @Y3 float declare @Z3 float declare @X4 float declare @Y4 float declare @Z4 float declare @X5 float declare @Y5 float declare @Z5 float declare @X6 float declare @Y6 float declare @Z6 float declare @X7 float declare @Y7 float declare @Z7 float declare @X8 float declare @Y8 float declare @Z8 float declare @query varchar(500) declare @starttime varchar(30) declare @endtime varchar(30) declare @coltagname varchar(300) declare @coltimestamp datetime declare @colavg varchar(38) declare @prevtime varchar(30) declare @currtime varchar(30) declare @breakflag int declare @firsttimeflag int set @firsttimeflag=0 declare @final float declare @initial float declare @event float Declare @digitalsql varchar(1000) set @final=0 set @initial=0 set @SQL ='SELECT tagname, timestamp , value,Quality FROM OPENQUERY(chemhist,'' set StartTime = '''''+ @StartDate +''''', EndTime = '''''+ @EndDate +''''', rowcount=0, samplingmode=RawByTime SELECT tagname, timestamp ,value,quality FROM ihRawData WHERE tagname =''''ADMIN-PC.Channel5.Device1.LIC1302'''' '')' INSERT INTO #tempval1 EXEC(@SQL) Set @Tagname='ADMIN-PC.Channel5.Device1.LIC1302' --Select @Tagname as TagName, [timestamp],CAST(tagval as float)as TagValue, --Cast(tagval as float)*11.08 as CalValue ,quality from --#tempval1 ORDER BY tagname, [timestamp] select top 1 @X1= Cast(tagval as float) from #tempval1 order by ID asc --select @X1 as FirstVal select top 1 @Y1=Cast(tagval as Float) from #tempval1 order by ID Desc --Select @Y1 as SecondVal set @Z1=(@Y1-@X1)*11.08 Insert into #temp1 values (@Z1) --SELECT @Z1 as LI1603 from #temp1 set @sql ='SELECT tagname, timestamp , value,Quality FROM OPENQUERY(chemhist,'' set StartTime = '''''+ @StartDate +''''', EndTime = '''''+ @EndDate +''''', rowcount=0, samplingmode=Calculated SELECT tagname, timestamp ,value,quality FROM ihRawData WHERE tagname =''''ADMIN-PC.Channel5.Device1.LIC1304'''' '')' INSERT INTO #tempval2 EXEC(@SQL) /*Select @Tagname as TagName,[timestamp],CAST(tagval as float)as TagValue, Cast(tagval as float)*6.02 as CalValue ,quality from #tempval2 ORDER BY tagname, [timestamp]*/ select top 1 @X2= Cast (tagval as Float) from #tempval2 order by ID asc select top 1 @Y2=Cast (tagval as Float) from #tempval2 order by ID desc set @Z2=(@Y2-@X2)*6.02 Insert into #temp2 values (@Z2) --SELECT @Z2 as LI1604 from #temp2 set @SQL ='SELECT tagname, timestamp , value,Quality FROM OPENQUERY(chemhist,'' set StartTime = '''''+ @StartDate +''''', EndTime = '''''+ @EndDate +''''', rowcount=0, samplingmode=RawByTime SELECT tagname, timestamp ,value,quality FROM ihRawData WHERE tagname =''''ADMIN-PC.Channel5.Device1.LIC1403'''' '')' INSERT INTO #tempval3 EXEC(@SQL) SET @Tagname='ADMIN-PC.Channel5.Device1.LIC1403' /* Select @Tagname as TagName,[timestamp],CAST(tagval as float)as TagValue, Cast(tagval as float)*4.80 as CalValue ,quality from #tempval3 ORDER BY tagname, [timestamp]*/ select top 1 @X3= Cast (tagval as Float) from #tempval3 order by ID asc --select @X3 as FirstValue Select top 1 @Y3=Cast (tagval as Float) from #tempval3 order by ID Desc --select @Y3 as secondvalue set @Z3=(@Y3-@X3)*4.80 Insert into #temp3 values (@Z3) --SELECT @Z3 as LI1607 from #temp3 set @query='select * from openquery(chemhist,''set StartTime="'+@starttime+'",EndTime="'+@endtime+'",SamplingMode=RawByTime,RowCount=0 select timestamp,value from ihrawdata where tagname=ADMIN-PC.Channel5.Device1.P65'')' insert into #tempdigival exec(@query) select @breakflag= COUNT(*) from #tempdigival where tagval=1 set @query='select * from openquery(chemhist,''set StartTime="'+@starttime+'",EndTime="'+@endtime+'",SamplingMode=RawByTime,RowCount=0 select tagname,timestamp,value from ihrawdata where tagname=ADMIN-PC.Channel5.Device1.LI1505'')' insert into #totalizervalue exec(@query) --take lost time from manual if (@breakflag>0) begin declare C1 cursor for select timestamp from #tempdigival where tagval=1 open C1 fetch next from C1 into @currtime while @@FETCH_STATUS=0 begin if (@firsttimeflag=0) begin set @firsttimeflag=1 select @initial=cast(tagval as float)from #totalizervalue where CONVERT(datetime2,waqt)=CONVERT(datetime2,dateadd(SECOND,10, CONVERT(datetime2,@starttime))) select top 1 @final=cast(tagval as float) from #totalizervalue where CONVERT(datetime2,waqt)<=CONVERT(datetime2,@currtime) order by waqt desc set @final=(@final-@initial) set @event=@final * 10 set @colavg=CONVERT(varchar,@event) insert into #tempsum values(@colavg,@final) set @prevtime=@currtime end else begin select top 1 @initial=cast(tagval as float) from #totalizervalue where CONVERT(datetime2,waqt)>=CONVERT(datetime2,@prevtime) select top 1 @final=cast(tagval as float) from #totalizervalue where CONVERT(datetime2,waqt)<=CONVERT(datetime2,@currtime) order by waqt desc set @final=(@final-@initial) set @colavg=CONVERT(varchar,@final) insert into #tempsum values(@colavg,@final) set @prevtime=@currtime end fetch next from C1 into @currtime end select top 1 @initial=cast(tagval as float) from #totalizervalue where CONVERT(datetime2,waqt)>=CONVERT(datetime2,@prevtime) select top 1 @final=cast(tagval as float) from #totalizervalue where CONVERT(datetime2,waqt)<=CONVERT(datetime2,@enddate) order by waqt desc set @final=(@final-@initial) set @event=@final * 10 set @colavg=CONVERT(varchar,@event) insert into #tempsum values(@colavg,@final) close C1 deallocate C1 select @colavg=SUM(CAST(tagval as float)) from #tempsum --truncate table #tempsum set @coltagname='ADMIN-PC.Channel5.Device1.LIC1503' set @coltimestamp=CONVERT(datetime,@endtime) insert into #tagtemp values(@coltimestamp,@coltagname,'PDS slurry Flow Totaliser - 9A','m3',@colavg,@final) end else begin select @initial=cast(tagval as float)from #totalizervalue where CONVERT(datetime2,waqt)=CONVERT(datetime2,dateadd(SECOND,10, CONVERT(datetime2,@starttime))) select top 1 @final=cast(tagval as float) from #totalizervalue where CONVERT(datetime2,waqt)<=CONVERT(datetime2,@enddate) order by waqt desc set @final=(@final-@initial) set @event=@final * 10 set @colavg=CONVERT(varchar,@event) set @coltagname='ADMIN-PC.Channel5.Device1.LI1505' set @coltimestamp=CONVERT(datetime,@endtime) insert into #tagtemp values(@coltimestamp,@coltagname,'PDS slurry Flow Totaliser - 9A','m3',@colavg,@final) end --SELECT @Z4 as LI1608 from #temp4 set @SQL ='SELECT tagname, timestamp , value,Quality FROM OPENQUERY(chemhist,'' set StartTime = '''''+ @StartDate +''''', EndTime = '''''+ @EndDate +''''', rowcount=0, samplingmode=RawByTime SELECT tagname, timestamp ,value,quality FROM ihRawData WHERE tagname =''''ADMIN-PC.Channel5.Device1.FIQ1302'''' '')' INSERT INTO #tempval EXEC(@SQL) --SET @Tagname='ADMIN-PC.Chem.Device1.LI1609' -- Select @Tagname,[timestamp],CAST(tagval as float)as TagValue, -- Cast(tagval as float)*73.30 as CalValue ,quality from --#tempval ORDER BY tagname, [timestamp] --select @calval=Cast (tagval as Float)*73.30 from #tempval -- Insert into #tagtemp values (@calval,@tagVal) select top 1 @X5= Cast (tagval as Float) from #tempval order by ID asc --select @X5 as FirstValue Select top 1 @Y5=Cast (tagval as Float) from #tempval order by ID Desc --select @Y5 as secondvalue set @Z5=(@Y5-@X5)*73.30 Insert into #temp5 values (@Z5) --SELECT @Z5 as LI1609 from #temp5 END set @avgtag=cast((isnull(@Z1,0)+isnull(@Z2,0)+isnull(@Z3,0)+isnull(@Z4,0)) as float)/cast((nullif((@Z5),0)) as float) insert into #tagavg values (@avgtag) --select * from #tagavg --inner join on table Begin insert into #finalResult select isnull(t1.Value,0) as R_21LT,isnull(t2.Value,0) as R_22LT,isnull(t3.Value,0) as R_24LT, isnull(t4.calevent,0) as B_22LT,isnull(t5.Value,0) as DETA_FIQ ,isnull(t6.avgtag,0) as avgtag from #temp1 as t1 Inner join #temp2 as t2 on t2.ID=t1.ID Inner join #temp3 as t3 on t3.ID=t2.ID Inner join #tagtemp as t4 on t4.ID=t3.ID inner join #temp5 as t5 on t5.ID=t4.ID inner join #tagavg as t6 on t6.ID=t5.ID select tag1,tag2,tag3,tag4,tag5,tag6 from #FinalResult End END
USE [cheminova] GO /****** Object: StoredProcedure [dbo].[SP_FinalYieldtemp] Script Date: 10/30/2014 10:22:51 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= ALTER PROCEDURE [dbo].[SP_FinalYieldtemp] -- Add the parameters for the stored procedure here @sdate datetime AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; --SET @sdate=convert(varchar (10),@sdate,103) declare @sql as varchar(1000) Begin create table #Temp1 ( [ID] [int] IDENTITY(1,1) NOT NULL, tag1 float, tag2 float, tag3 float, tag4 float, tag5 float, tag6 float ) END -- Insert statements for procedure here SET @SQL ='SELECT * FROM OPENROWSET(''SQLNCLI'', ''Server=ADMIN-PC;Trusted_Connection=yes;'', ''SET FMTONLY OFF; EXEC [cheminova].[dbo].[SP_Firstdistil] "'+convert(varchar(20),@sdate,103)+'" '')' --select @SQL EXEC (@SQL) --INSERT into #Temp1 EXEC (@SQL) END
exec SP_FinalYieldtemp '2014-10-29'