触发器(八)Trigger_Insert_PollHour

USE [AirDB_YiZhuang]
GO


/****** Object:  Trigger [dbo].[Trigger_Insert_PollHour]    Script Date: 2017/12/25 17:08:25 ******/
SET ANSI_NULLS ON
GO


SET QUOTED_IDENTIFIER ON
GO




-- =============================================
-- Author: pyy
-- Create date: 2017-2-15
-- Description: min数据求平均得到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 [AirDB_YiZhuang].[dbo].[T_Data_PollHour])不用加1了
--where MONIDATE >=(select max(MONIDATE) from [AirDB_YiZhuang].[dbo].[T_Data_PollHour])
-- =============================================
CREATE TRIGGER [dbo].[Trigger_Insert_PollHour] 
   ON  [dbo].[T_Data_PollMin]
   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_PollMin] where STATIONCODE= '500000001')
--编号写错了5000001
--站点2的最新时间
set @t2=(select max(MONIDATE) from [AirDB_YiZhuang].[dbo].[T_Data_PollMin] where STATIONCODE= '500000002')
--5000002
--最新时间里的最小值
set @Mint=@t1
--(select case when @t1<@t2 then @t1 else @t2 end)
insert into [AirDB_YiZhuang].[dbo].[T_Data_PollHour]
(STATIONCODE,MONIDATE,NOx,NH3,TNX,H2S)
select STATIONCODE,DATEADD(hour,1,cast(MONIDATE as datetime)) MONIDATE,NOx,NH3,TNX,H2S
from
(select STATIONCODE,
substring(convert(varchar,MONIDATE,120),0,14)+':00:00' MONIDATE,
AVG(NOx) NOx,AVG(NH3) NH3,
AVG(TNX) TNX,AVG(H2S) H2S
from 
[AirDB_YiZhuang].[dbo].[T_Data_PollMin]
where MONIDATE >=(select max(MONIDATE) from [AirDB_YiZhuang].[dbo].[T_Data_PollHour])
--T_MID_AIRQUALITYHOUR最新的时间
 and 
 --MONIDATE <(select substring(convert(varchar,max(monidate),120),0,14)+':00:00' from T_MID_AIRQUALITYMin)
--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