触发器(五)Trigger_Insert_VocHour

USE [AirDB_YiZhuang]
GO


/****** Object:  Trigger [dbo].[Trigger_Insert_VocHour]    Script Date: 2017/12/25 16:54:10 ******/
SET ANSI_NULLS ON
GO


SET QUOTED_IDENTIFIER ON
GO






-- =============================================
-- Author: pyy
-- Create date: 2016-9-27
-- Description: VocMin数据求平均得到hour
---- mondify Description: 2017-4-5站点“宾士国际”的数据只更新到了3-28
--DATEADD(hour,1,cast(MONIDATE as datetime))小时求平均,加1小时
--where MONIDATE >=(select DATEADD(HOUR,1,max(MONIDATE)) from T_Data_VocHour不用加1了
--where MONIDATE >=(select max(MONIDATE) from T_Data_VocHour)
-- =============================================
CREATE TRIGGER [dbo].[Trigger_Insert_VocHour] 
   ON  [dbo].[T_Data_VocMin]
   AFTER INSERT--,DELETE,UPDATE
AS 
BEGIN
declare @t1 datetime
declare @t2 datetime
declare @Mint datetime
--站点1的最新时间
set @t1=(select max(MONIDATE) from [AirDB_YiZhuang].[dbo].[T_Data_VocMin] where STATIONCODE= '500000001')
--编号写错了5000001
--站点2的最新时间
set @t2=(select max(MONIDATE) from [AirDB_YiZhuang].[dbo].[T_Data_VocMin] where STATIONCODE= '500000002')
--5000002
--最新时间里的最小值
set @Mint=@t1
--(select case when @t1<@t2 then @t1 else @t2 end)
insert into [AirDB_YiZhuang].[dbo].[T_Data_VocHour]
(STATIONCODE,MONIDATE,Vinyl_chloride,Butadiene,Ethylene,
ethane,Propane,propylene,butylene,twoButene,cistwoButene,transtwoPentene,cistwoPentene,onePentene,
Isoprene,IsoButene,nbutane,Isopentane,nPentane,dimethylbutane,symDichloroethane,Methylpentane,
nHexane,Benzene,cyclohexane,nHeptane,octane,nNonane,Cumene,nPropylbenzene,trimethylbenzene,
Mesitylene,Chlorobenzene,jiandichlorobenzene,duidichlorobenzene,lindichlorobenzene,tetrachloroethylene,
trichloroethylene,cyclopentane,decane,Diethylbenzene,duiDiethylbenzene,ttdimethylpentane,tfdimethylpentane,
methylcyclopentane,methylheptane,methylhexane,trimethylpentane,undecane)
select STATIONCODE,DATEADD(hour,1,cast(MONIDATE as datetime)) MONIDATE,Vinyl_chloride,Butadiene,Ethylene,
ethane,Propane,propylene,butylene,twoButene,cistwoButene,transtwoPentene,cistwoPentene,onePentene,
Isoprene,IsoButene,nbutane,Isopentane,nPentane,dimethylbutane,symDichloroethane,Methylpentane,
nHexane,Benzene,cyclohexane,nHeptane,octane,nNonane,Cumene,nPropylbenzene,trimethylbenzene,
Mesitylene,Chlorobenzene,jiandichlorobenzene,duidichlorobenzene,lindichlorobenzene,tetrachloroethylene,
trichloroethylene,cyclopentane,decane,Diethylbenzene,duiDiethylbenzene,ttdimethylpentane,tfdimethylpentane,
methylcyclopentane,methylheptane,methylhexane,trimethylpentane,undecane
from
(select STATIONCODE,
substring(convert(varchar,MONIDATE,120),0,14)+':00:00' MONIDATE,
AVG(Vinyl_chloride) Vinyl_chloride,AVG(Butadiene) Butadiene,
AVG(Ethylene) Ethylene,AVG(ethane) ethane,
AVG(Propane) Propane,AVG(propylene) propylene,
AVG(butylene) butylene,AVG(twoButene) twoButene,
AVG(cistwoButene) cistwoButene,AVG(transtwoPentene) transtwoPentene,
AVG(cistwoPentene) cistwoPentene,AVG(onePentene) onePentene,
AVG(Isoprene) Isoprene,AVG(IsoButene) IsoButene,
AVG(nbutane) nbutane,AVG(Isopentane) Isopentane,
AVG(nPentane) nPentane,AVG(dimethylbutane) dimethylbutane,
AVG(symDichloroethane) symDichloroethane,AVG(Methylpentane) Methylpentane,


AVG(nHexane) nHexane,AVG(Benzene) Benzene,
AVG(cyclohexane) cyclohexane,AVG(nHeptane) nHeptane,
AVG(octane) octane,AVG(nNonane) nNonane,
AVG(Cumene) Cumene,AVG(nPropylbenzene) nPropylbenzene,
AVG(trimethylbenzene) trimethylbenzene,AVG(Mesitylene) Mesitylene,
AVG(Chlorobenzene) Chlorobenzene,AVG(jiandichlorobenzene) jiandichlorobenzene,
AVG(duidichlorobenzene) duidichlorobenzene,AVG(lindichlorobenzene) lindichlorobenzene,
AVG(tetrachloroethylene) tetrachloroethylene,AVG(trichloroethylene) trichloroethylene,
AVG(cyclopentane) cyclopentane,AVG(decane) decane,
AVG(Diethylbenzene) Diethylbenzene,AVG(duiDiethylbenzene) duiDiethylbenzene,


AVG(ttdimethylpentane) ttdimethylpentane,AVG(tfdimethylpentane) tfdimethylpentane,
AVG(methylcyclopentane) methylcyclopentane,AVG(methylheptane) methylheptane,
AVG(methylhexane) methylhexane,AVG(trimethylpentane) trimethylpentane,
AVG(undecane) undecane




from 
[dbo].[T_Data_VocMin]
where MONIDATE >=(select max(MONIDATE) from T_Data_VocHour)
--T_MID_AIRQUALITYHOUR最新的时间
 and 
 --MONIDATE <(select substring(convert(varchar,max(monidate),120),0,14)+':00:00' from T_Data_VocMin)
--T_MID_AIRQUALITYMin最新的时间的小时值
--当两个站点的分钟数据不同步的时候,数据慢的那个站点的小时平均数据就可能不对(不足60条分钟数据)
MONIDATE <(substring(convert(varchar,@Mint,120),0,14)+':00:00')
group by STATIONCODE,substring(convert(varchar,MONIDATE,120),0,14)) as a


END






GO