触发器(十七)Trigger_Insert_ForeDutyLogSixT
来源：互联网 发布：淘宝怎么发布微淘广播 编辑：程序博客网 时间：2024/06/05 11:34
USE [AirDB_LiuZhou]
GO
/****** Object: Trigger [dbo].[Trigger_Insert_ForeDutyLogSixT] Script Date: 2017-12-26 10:03:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: pyy
-- Create date: 2016-10-13
-- Description: 发布时自动保存‘预报值班登记’(预报发布时，当天4天的数据还没入库，就没有保存),
--所以再设置一个基于[dbo].[T_MID_AIRQUALITYHOUR]的触发器
-- =============================================
CREATE TRIGGER [dbo].[Trigger_Insert_ForeDutyLogSixT]
ON [dbo].[T_MID_AIRQUALITYHOUR]
AFTER INSERT--,DELETE,UPDATE
AS
BEGIN
declare @date datetime
declare @hour datetime
--获取当天的日期"yyyy-mm-dd"
set @date=substring(convert(varchar,GETDATE(),120),0,11)
set @hour=(select max(monidate) from T_MID_AIRQUALITYHOUR)
--先删除当天的‘预报值班登记’数据，再重新插入
--删除
delete from [AirDB_LiuZhou].[dbo].[T_Data_ForeDutyLog] where DutyTime=@date
--插入
insert into [AirDB_LiuZhou].[dbo].[T_Data_ForeDutyLog]
(DutyTime,DutyPeople,PreAQI,PreLevel,PrePoll,TenAQI,TenLevel,TenPoll,
SixTAQI,SixTLevel,SixTPoll,AQI_24_model,Poll_24_model,AQI_48_model,Poll_48_model,AQI_72_model,Poll_72_model,
h_500,h_700,h_850,DM,weather,wind,humidity,air,
h_500_24,h_700_24,h_850_24,DM_24,weather_24,wind_24,humidity_24,air_24,
h_500_48,h_700_48,h_850_48,DM_48,weather_48,wind_48,humidity_48,air_48,
h_500_72,h_700_72,h_850_72,DM_72,weather_72,wind_72,humidity_72,air_72,
AQI_24,Poll_24,AQI_48,Poll_48,AQI_72,Poll_72,
affect_pub, affect_qx,affect_yj)
select
pu.DutyTime,
--case LEN(pu.DutyPeople) when 0 then '高级管理员' else pu.DutyPeople end DutyPeople,
pu.DutyPeople,
c.AQI PreAQI,c.AIRLEVEL PreLevel,SUBSTRING(c.PRIMARYPOLLUTANT,0,LEN(c.PRIMARYPOLLUTANT)) PrePoll,
a.AQI TenAQI,SUBSTRING(a.PRIMARYPOLLUTANT,0,LEN(a.PRIMARYPOLLUTANT)) TenPoll,
a.AIRLEVEL TenLevel
,b.AQI SixTAQI,SUBSTRING(b.PRIMARYPOLLUTANT,0,LEN(b.PRIMARYPOLLUTANT)) SixTPoll,
b.AIRLEVEL SixTLevel,
m.AQI_24_model AQI_24_model,m.Poll_24_model Poll_24_model,
m.AQI_48_model AQI_48_model,m.Poll_48_model Poll_48_model,
m.AQI_72_model AQI_72_model,m.Poll_72_model Poll_72_model,
f.h_500 h_500,f.h_700 h_700,f.h_850 h_850,f.DM DM,f.weather weather,f.wind wind,f.humidity humidity,f.air air,
f.h_500_24 h_500_24,f.h_700_24 h_700_24,f.h_850_24 h_850_24,f.DM_24 DM_24,f.weather_24 weather_24,
f.wind_24 wind_24,f.humidity_24 humidity_24,f.air_24 air_24,
f.h_500_48 h_500_48,f.h_700_48 h_700_48,f.h_850_48 h_850_48,f.DM_48 DM_48,f.weather_48 weather_48,
f.wind_48 wind_48,f.humidity_48 humidity_48,f.air_48 air_48,
f.h_500_72 h_500_72,f.h_700_72 h_700_72,f.h_850_72 h_850_72,f.DM_72 DM_72,f.weather_72 weather_72,
f.wind_72 wind_72,f.humidity_72 humidity_72,f.air_72 air_72
,pu.AQI_24,pu.Poll_24,pu.AQI_48,pu.Poll_48,pu.AQI_72,pu.Poll_72
,case pu.AFFECT
when '1' then '正常会商发布■'
else '正常会商发布□' end affect_pub
,case pu.AFFECT
when '21' then '启动气象会商■'
else '启动气象会商□' end affect_qx
,case pu.AFFECT
when '31' then '启动预警会商■'
else '启动预警会商' end affect_yj
from
--**part1**--
---当天10点的小时数据
(select
--计算AQI
[dbo].[fun_AirQualityIndex]([dbo].[co2iaqi](AVG(CO)),[dbo].[so22iaqi](AVG(SO2)),
[dbo].[no22iaqi](AVG(NO2)),[dbo].[o32iaqi](AVG(O3)),[dbo].[o32iaqi](AVG(O3)),
[dbo].[pm252iaqi](AVG(PM25)),[dbo].[pm102iaqi](AVG(PM10))) AQI,
--计算 首要污染物
[dbo].[fun_AQI_FristPollutant]([dbo].[co2iaqi](AVG(CO)),[dbo].[so22iaqi](AVG(SO2)),
[dbo].[no22iaqi](AVG(NO2)),[dbo].[o32iaqi](AVG(O3)),[dbo].[o32iaqi](AVG(O3)),
[dbo].[pm252iaqi](AVG(PM25)),[dbo].[pm102iaqi](AVG(PM10))) PRIMARYPOLLUTANT,
--空气质量等级
[dbo].[fun_AirAQIdegree]([dbo].[fun_AirQualityIndex]([dbo].[co2iaqi](AVG(CO)),[dbo].[so22iaqi](AVG(SO2)),
[dbo].[no22iaqi](AVG(NO2)),[dbo].[o32iaqi](AVG(O3)),[dbo].[o32iaqi](AVG(O3)),
[dbo].[pm252iaqi](AVG(PM25)),[dbo].[pm102iaqi](AVG(PM10)))) AIRLEVEL
from T_MID_AIRQUALITYHOUR
where MONIDATE=(substring(convert(varchar,GETDATE(),120),0,12)+'10:00:00')
group by MONIDATE) as a
--**part1--end**--
join
--**part2**--
--当天16点的数据
(select
--计算AQI
[dbo].[fun_AirQualityIndex]([dbo].[co2iaqi](AVG(CO)),[dbo].[so22iaqi](AVG(SO2)),
[dbo].[no22iaqi](AVG(NO2)),[dbo].[o32iaqi](AVG(O3)),[dbo].[o32iaqi](AVG(O3)),
[dbo].[pm252iaqi](AVG(PM25)),[dbo].[pm102iaqi](AVG(PM10))) AQI,
--计算 首要污染物
[dbo].[fun_AQI_FristPollutant]([dbo].[co2iaqi](AVG(CO)),[dbo].[so22iaqi](AVG(SO2)),
[dbo].[no22iaqi](AVG(NO2)),[dbo].[o32iaqi](AVG(O3)),[dbo].[o32iaqi](AVG(O3)),
[dbo].[pm252iaqi](AVG(PM25)),[dbo].[pm102iaqi](AVG(PM10))) PRIMARYPOLLUTANT,
--空气质量等级
[dbo].[fun_AirAQIdegree]([dbo].[fun_AirQualityIndex]([dbo].[co2iaqi](AVG(CO)),[dbo].[so22iaqi](AVG(SO2)),
[dbo].[no22iaqi](AVG(NO2)),[dbo].[o32iaqi](AVG(O3)),[dbo].[o32iaqi](AVG(O3)),
[dbo].[pm252iaqi](AVG(PM25)),[dbo].[pm102iaqi](AVG(PM10)))) AIRLEVEL
from T_MID_AIRQUALITYHOUR
where MONIDATE=(substring(convert(varchar,GETDATE(),120),0,12)+'16:00:00')
group by MONIDATE) as b
on 1=1
--**part2_end**--
join
--**part3**--
--前一天的日数据(O3_8H要参与计算:AQI,)
(select
--计算AQI
[dbo].[fun_AirQualityIndex]([dbo].[co2iaqi_24h](AVG(CO)),[dbo].[so22iaqi_24h](AVG(SO2)),
[dbo].[no22iaqi_24h](AVG(NO2)),[dbo].[o32iaqi](AVG(O3)),[dbo].[o3_8h2iaqi](AVG(O38H)),
[dbo].[pm252iaqi](AVG(PM25)),[dbo].[pm102iaqi](AVG(PM10))) AQI,
--计算 首要污染物
[dbo].[fun_AQI_FristPollutantDay]([dbo].[co2iaqi_24h](AVG(CO)),[dbo].[so22iaqi_24h](AVG(SO2)),
[dbo].[no22iaqi_24h](AVG(NO2)),[dbo].[o32iaqi](AVG(O3)),[dbo].[o3_8h2iaqi](AVG(O38H)),
[dbo].[pm252iaqi](AVG(PM25)),[dbo].[pm102iaqi](AVG(PM10))) PRIMARYPOLLUTANT,
--空气质量等级
[dbo].[fun_AirAQIdegree]([dbo].[fun_AirQualityIndex]([dbo].[co2iaqi_24h](AVG(CO)),[dbo].[so22iaqi_24h](AVG(SO2)),
[dbo].[no22iaqi_24h](AVG(NO2)),[dbo].[o32iaqi](AVG(O3)),[dbo].[o3_8h2iaqi](AVG(O38H)),
[dbo].[pm252iaqi](AVG(PM25)),[dbo].[pm102iaqi](AVG(PM10)))) AIRLEVEL
from T_MID_AIRQUALITYDAY
where MONIDATE=substring(convert(varchar,DATEADD(DAY,-1,GETDATE()),120),0,12)
group by MONIDATE) as c
on 1=1
--**part3_end**--
join
--**part4**--
--模式预报数据(O3_8H要参与计算:AQI,)
(select md1.AQI_24_model,
SUBSTRING(md1.Poll_24_model,0,LEN(md1.Poll_24_model)) Poll_24_model,
md2.AQI_48_model,
SUBSTRING(md2.Poll_48_model,0,LEN(md2.Poll_48_model)) Poll_48_model,
md3.AQI_72_model,
SUBSTRING(md3.Poll_72_model,0,LEN(md3.Poll_72_model)) Poll_72_model from
--24时
(select m1.AQI AQI_24_model,m1.PRIMARYPOLLUTANT Poll_24_model from
(select
--计算AQI
[dbo].[fun_AirQualityIndex]([dbo].[co2iaqi_24h](AVG(CO)),[dbo].[so22iaqi_24h](AVG(SO2)),
[dbo].[no22iaqi_24h](AVG(NO2)),[dbo].[o32iaqi](AVG(O3)),[dbo].[o3_8h2iaqi](AVG(O38H)),
[dbo].[pm252iaqi](AVG(PM25)),[dbo].[pm102iaqi](AVG(PM10))) AQI,
--计算 首要污染物
[dbo].[fun_AQI_FristPollutantDay]([dbo].[co2iaqi_24h](AVG(CO)),[dbo].[so22iaqi_24h](AVG(SO2)),
[dbo].[no22iaqi_24h](AVG(NO2)),[dbo].[o32iaqi](AVG(O3)),[dbo].[o3_8h2iaqi](AVG(O38H)),
[dbo].[pm252iaqi](AVG(PM25)),[dbo].[pm102iaqi](AVG(PM10))) PRIMARYPOLLUTANT,
DATATYPE
from T_MID_FORECASTSTATISTICS
where MONITORTIME=substring(convert(varchar,GETDATE(),120),0,11) and MODEL='1'
group by MONITORTIME,FORECASTTIME,DATATYPE) as m1 where m1.DATATYPE='24') as md1
join
--48时
(select m2.AQI AQI_48_model,m2.PRIMARYPOLLUTANT Poll_48_model from
(select
--计算AQI
[dbo].[fun_AirQualityIndex]([dbo].[co2iaqi_24h](AVG(CO)),[dbo].[so22iaqi_24h](AVG(SO2)),
[dbo].[no22iaqi_24h](AVG(NO2)),[dbo].[o32iaqi](AVG(O3)),[dbo].[o3_8h2iaqi](AVG(O38H)),
[dbo].[pm252iaqi](AVG(PM25)),[dbo].[pm102iaqi](AVG(PM10))) AQI,
--计算 首要污染物
[dbo].[fun_AQI_FristPollutantDay]([dbo].[co2iaqi_24h](AVG(CO)),[dbo].[so22iaqi_24h](AVG(SO2)),
[dbo].[no22iaqi_24h](AVG(NO2)),[dbo].[o32iaqi](AVG(O3)),[dbo].[o3_8h2iaqi](AVG(O38H)),
[dbo].[pm252iaqi](AVG(PM25)),[dbo].[pm102iaqi](AVG(PM10))) PRIMARYPOLLUTANT,
DATATYPE
from T_MID_FORECASTSTATISTICS
where MONITORTIME=substring(convert(varchar,GETDATE(),120),0,11) and MODEL='1'
group by MONITORTIME,FORECASTTIME,DATATYPE) as m2 where m2.DATATYPE='48') as md2
on 1=1
join
--72时
(select m3.AQI AQI_72_model,m3.PRIMARYPOLLUTANT Poll_72_model from
(select
--计算AQI
[dbo].[fun_AirQualityIndex]([dbo].[co2iaqi_24h](AVG(CO)),[dbo].[so22iaqi_24h](AVG(SO2)),
[dbo].[no22iaqi_24h](AVG(NO2)),[dbo].[o32iaqi](AVG(O3)),[dbo].[o3_8h2iaqi](AVG(O38H)),
[dbo].[pm252iaqi](AVG(PM25)),[dbo].[pm102iaqi](AVG(PM10))) AQI,
--计算 首要污染物
[dbo].[fun_AQI_FristPollutantDay]([dbo].[co2iaqi_24h](AVG(CO)),[dbo].[so22iaqi_24h](AVG(SO2)),
[dbo].[no22iaqi_24h](AVG(NO2)),[dbo].[o32iaqi](AVG(O3)),[dbo].[o3_8h2iaqi](AVG(O38H)),
[dbo].[pm252iaqi](AVG(PM25)),[dbo].[pm102iaqi](AVG(PM10))) PRIMARYPOLLUTANT,
DATATYPE
from T_MID_FORECASTSTATISTICS
where MONITORTIME=substring(convert(varchar,GETDATE(),120),0,11) and MODEL='1'
group by MONITORTIME,FORECASTTIME,DATATYPE) as m3 where m3.DATATYPE='72') as md3
on 1=1
) as m
on 1=1
--**part4_end**--
join
--**part5**--
--气象数据
(
select
MONITORTIME
--实测气象数据
,(select hPa_500 from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=0) h_500--一气象实况h_500
,(select hPa_700 from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=0) h_700--一气象实况h_700
,(select hPa_850 from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=0) h_850--一气象实况h_850
,(select GROUND from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=0) DM--一气象实况DM
,(select WEATHER from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=0) weather--一气象实况weather
,(select WINDSPEED from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=0) wind--一气象实况wind
,(select RELATIVEHUMIDITY from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=0) humidity--一气象实况humidity
,(select DIFFUSIONCONDITIONS from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=0) air--一气象实况air
--,(select hPa_500 from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
--and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=1) h_500_24--一24时
--24气象数据
,(select hPa_500 from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=1) h_500_24--一24时h_500
,(select hPa_700 from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=1) h_700_24--一24时h_700
,(select hPa_850 from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=1) h_850_24--一24时h_850
,(select GROUND from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=1) DM_24--一24时DM
,(select WEATHER from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=1) weather_24--一24时weather
,(select WINDSPEED from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=1) wind_24--一24时wind
,(select RELATIVEHUMIDITY from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=1) humidity_24--一24时humidity
,(select DIFFUSIONCONDITIONS from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=1) air_24--一24时air
--,(select hPa_500 from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
--and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=2) --一48时
--48气象数据
,(select hPa_500 from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=2) h_500_48--一48时h_500
,(select hPa_700 from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=2) h_700_48--一48时h_700
,(select hPa_850 from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=2) h_850_48--一48时h_850
,(select GROUND from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=2) DM_48--一48时DM
,(select WEATHER from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=2) weather_48--一48时weather
,(select WINDSPEED from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=2) wind_48--一48时wind
,(select RELATIVEHUMIDITY from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=2) humidity_48--一48时humidity
,(select DIFFUSIONCONDITIONS from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=2) air_48--一48时air
--,(select hPa_500 from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
--and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=3) h_500_48--一72时
--72气象数据
,(select hPa_500 from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=3) h_500_72--一72时h_500
,(select hPa_700 from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=3) h_700_72--一72时h_700
,(select hPa_850 from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=3) h_850_72--一72时h_850
,(select GROUND from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=3) DM_72--一72时DM
,(select WEATHER from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=3) weather_72--一72时weather
,(select WINDSPEED from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=3) wind_72--一72时wind
,(select RELATIVEHUMIDITY from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=3) humidity_72--一72时humidity
,(select DIFFUSIONCONDITIONS from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=3) air_72--一72时air
from T_MID_METEOROLOGICALOBSERVATIONS t
where MONITORTIME=substring(convert(varchar,GETDATE(),120),0,11) and MODEL='10'
group by MONITORTIME
) as f
on 1=1
--**part5_end**--
join
--**part6**--
--发布数据
(select p.PUBLISHTIME DutyTime,
CASE WHEN p.FORECASTER is null THEN '高级管理员' ELSE p.FORECASTER END
DutyPeople
--p.FORECASTER DutyPeople
,p.AFFECT AFFECT
,convert(varchar,p.AQI1_24,120)+'～'+convert(varchar,AQI2_24,120) AQI_24,p.Poll_24 Poll_24
,convert(varchar,p.AQI1_48,120)+'～'+convert(varchar,AQI2_48,120) AQI_48,p.Poll_48 Poll_48
,convert(varchar,p.AQI1_72,120)+'～'+convert(varchar,AQI2_72,120) AQI_72,p.Poll_72 Poll_72
from
(select
PUBLISHTIME,FORECASTER,AFFECT
,(select AQI1 from T_MID_FORECASTPUBLISH where PUBLISHTIME=t.PUBLISHTIME
and DATEDIFF(DAY,PUBLISHTIME,FORECASTTIME)=1) AQI1_24
,(select AQI2 from T_MID_FORECASTPUBLISH where PUBLISHTIME=t.PUBLISHTIME
and DATEDIFF(DAY,PUBLISHTIME,FORECASTTIME)=1) AQI2_24
,(select PRIMARYPOLLUTANT from T_MID_FORECASTPUBLISH where PUBLISHTIME=t.PUBLISHTIME
and DATEDIFF(DAY,PUBLISHTIME,FORECASTTIME)=1) Poll_24
,(select AQI1 from T_MID_FORECASTPUBLISH where PUBLISHTIME=t.PUBLISHTIME
and DATEDIFF(DAY,PUBLISHTIME,FORECASTTIME)=2) AQI1_48
,(select AQI2 from T_MID_FORECASTPUBLISH where PUBLISHTIME=t.PUBLISHTIME
and DATEDIFF(DAY,PUBLISHTIME,FORECASTTIME)=2) AQI2_48
,(select PRIMARYPOLLUTANT from T_MID_FORECASTPUBLISH where PUBLISHTIME=t.PUBLISHTIME
and DATEDIFF(DAY,PUBLISHTIME,FORECASTTIME)=2) Poll_48
,(select AQI1 from T_MID_FORECASTPUBLISH where PUBLISHTIME=t.PUBLISHTIME
and DATEDIFF(DAY,PUBLISHTIME,FORECASTTIME)=3) AQI1_72
,(select AQI2 from T_MID_FORECASTPUBLISH where PUBLISHTIME=t.PUBLISHTIME
and DATEDIFF(DAY,PUBLISHTIME,FORECASTTIME)=3) AQI2_72
,(select PRIMARYPOLLUTANT from T_MID_FORECASTPUBLISH where PUBLISHTIME=t.PUBLISHTIME
and DATEDIFF(DAY,PUBLISHTIME,FORECASTTIME)=3) Poll_72
from T_MID_FORECASTPUBLISH t
where PUBLISHTIME=substring(convert(varchar,GETDATE(),120),0,11)
group by PUBLISHTIME,FORECASTER,AFFECT
) as p) as pu
on 1=1
--**part6_end**--
where @hour>=(substring(convert(varchar,GETDATE(),120),0,12)+'16:00:00')
END
GO
GO
/****** Object: Trigger [dbo].[Trigger_Insert_ForeDutyLogSixT] Script Date: 2017-12-26 10:03:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: pyy
-- Create date: 2016-10-13
-- Description: 发布时自动保存‘预报值班登记’(预报发布时，当天4天的数据还没入库，就没有保存),
--所以再设置一个基于[dbo].[T_MID_AIRQUALITYHOUR]的触发器
-- =============================================
CREATE TRIGGER [dbo].[Trigger_Insert_ForeDutyLogSixT]
ON [dbo].[T_MID_AIRQUALITYHOUR]
AFTER INSERT--,DELETE,UPDATE
AS
BEGIN
declare @date datetime
declare @hour datetime
--获取当天的日期"yyyy-mm-dd"
set @date=substring(convert(varchar,GETDATE(),120),0,11)
set @hour=(select max(monidate) from T_MID_AIRQUALITYHOUR)
--先删除当天的‘预报值班登记’数据，再重新插入
--删除
delete from [AirDB_LiuZhou].[dbo].[T_Data_ForeDutyLog] where DutyTime=@date
--插入
insert into [AirDB_LiuZhou].[dbo].[T_Data_ForeDutyLog]
(DutyTime,DutyPeople,PreAQI,PreLevel,PrePoll,TenAQI,TenLevel,TenPoll,
SixTAQI,SixTLevel,SixTPoll,AQI_24_model,Poll_24_model,AQI_48_model,Poll_48_model,AQI_72_model,Poll_72_model,
h_500,h_700,h_850,DM,weather,wind,humidity,air,
h_500_24,h_700_24,h_850_24,DM_24,weather_24,wind_24,humidity_24,air_24,
h_500_48,h_700_48,h_850_48,DM_48,weather_48,wind_48,humidity_48,air_48,
h_500_72,h_700_72,h_850_72,DM_72,weather_72,wind_72,humidity_72,air_72,
AQI_24,Poll_24,AQI_48,Poll_48,AQI_72,Poll_72,
affect_pub, affect_qx,affect_yj)
select
pu.DutyTime,
--case LEN(pu.DutyPeople) when 0 then '高级管理员' else pu.DutyPeople end DutyPeople,
pu.DutyPeople,
c.AQI PreAQI,c.AIRLEVEL PreLevel,SUBSTRING(c.PRIMARYPOLLUTANT,0,LEN(c.PRIMARYPOLLUTANT)) PrePoll,
a.AQI TenAQI,SUBSTRING(a.PRIMARYPOLLUTANT,0,LEN(a.PRIMARYPOLLUTANT)) TenPoll,
a.AIRLEVEL TenLevel
,b.AQI SixTAQI,SUBSTRING(b.PRIMARYPOLLUTANT,0,LEN(b.PRIMARYPOLLUTANT)) SixTPoll,
b.AIRLEVEL SixTLevel,
m.AQI_24_model AQI_24_model,m.Poll_24_model Poll_24_model,
m.AQI_48_model AQI_48_model,m.Poll_48_model Poll_48_model,
m.AQI_72_model AQI_72_model,m.Poll_72_model Poll_72_model,
f.h_500 h_500,f.h_700 h_700,f.h_850 h_850,f.DM DM,f.weather weather,f.wind wind,f.humidity humidity,f.air air,
f.h_500_24 h_500_24,f.h_700_24 h_700_24,f.h_850_24 h_850_24,f.DM_24 DM_24,f.weather_24 weather_24,
f.wind_24 wind_24,f.humidity_24 humidity_24,f.air_24 air_24,
f.h_500_48 h_500_48,f.h_700_48 h_700_48,f.h_850_48 h_850_48,f.DM_48 DM_48,f.weather_48 weather_48,
f.wind_48 wind_48,f.humidity_48 humidity_48,f.air_48 air_48,
f.h_500_72 h_500_72,f.h_700_72 h_700_72,f.h_850_72 h_850_72,f.DM_72 DM_72,f.weather_72 weather_72,
f.wind_72 wind_72,f.humidity_72 humidity_72,f.air_72 air_72
,pu.AQI_24,pu.Poll_24,pu.AQI_48,pu.Poll_48,pu.AQI_72,pu.Poll_72
,case pu.AFFECT
when '1' then '正常会商发布■'
else '正常会商发布□' end affect_pub
,case pu.AFFECT
when '21' then '启动气象会商■'
else '启动气象会商□' end affect_qx
,case pu.AFFECT
when '31' then '启动预警会商■'
else '启动预警会商' end affect_yj
from
--**part1**--
---当天10点的小时数据
(select
--计算AQI
[dbo].[fun_AirQualityIndex]([dbo].[co2iaqi](AVG(CO)),[dbo].[so22iaqi](AVG(SO2)),
[dbo].[no22iaqi](AVG(NO2)),[dbo].[o32iaqi](AVG(O3)),[dbo].[o32iaqi](AVG(O3)),
[dbo].[pm252iaqi](AVG(PM25)),[dbo].[pm102iaqi](AVG(PM10))) AQI,
--计算 首要污染物
[dbo].[fun_AQI_FristPollutant]([dbo].[co2iaqi](AVG(CO)),[dbo].[so22iaqi](AVG(SO2)),
[dbo].[no22iaqi](AVG(NO2)),[dbo].[o32iaqi](AVG(O3)),[dbo].[o32iaqi](AVG(O3)),
[dbo].[pm252iaqi](AVG(PM25)),[dbo].[pm102iaqi](AVG(PM10))) PRIMARYPOLLUTANT,
--空气质量等级
[dbo].[fun_AirAQIdegree]([dbo].[fun_AirQualityIndex]([dbo].[co2iaqi](AVG(CO)),[dbo].[so22iaqi](AVG(SO2)),
[dbo].[no22iaqi](AVG(NO2)),[dbo].[o32iaqi](AVG(O3)),[dbo].[o32iaqi](AVG(O3)),
[dbo].[pm252iaqi](AVG(PM25)),[dbo].[pm102iaqi](AVG(PM10)))) AIRLEVEL
from T_MID_AIRQUALITYHOUR
where MONIDATE=(substring(convert(varchar,GETDATE(),120),0,12)+'10:00:00')
group by MONIDATE) as a
--**part1--end**--
join
--**part2**--
--当天16点的数据
(select
--计算AQI
[dbo].[fun_AirQualityIndex]([dbo].[co2iaqi](AVG(CO)),[dbo].[so22iaqi](AVG(SO2)),
[dbo].[no22iaqi](AVG(NO2)),[dbo].[o32iaqi](AVG(O3)),[dbo].[o32iaqi](AVG(O3)),
[dbo].[pm252iaqi](AVG(PM25)),[dbo].[pm102iaqi](AVG(PM10))) AQI,
--计算 首要污染物
[dbo].[fun_AQI_FristPollutant]([dbo].[co2iaqi](AVG(CO)),[dbo].[so22iaqi](AVG(SO2)),
[dbo].[no22iaqi](AVG(NO2)),[dbo].[o32iaqi](AVG(O3)),[dbo].[o32iaqi](AVG(O3)),
[dbo].[pm252iaqi](AVG(PM25)),[dbo].[pm102iaqi](AVG(PM10))) PRIMARYPOLLUTANT,
--空气质量等级
[dbo].[fun_AirAQIdegree]([dbo].[fun_AirQualityIndex]([dbo].[co2iaqi](AVG(CO)),[dbo].[so22iaqi](AVG(SO2)),
[dbo].[no22iaqi](AVG(NO2)),[dbo].[o32iaqi](AVG(O3)),[dbo].[o32iaqi](AVG(O3)),
[dbo].[pm252iaqi](AVG(PM25)),[dbo].[pm102iaqi](AVG(PM10)))) AIRLEVEL
from T_MID_AIRQUALITYHOUR
where MONIDATE=(substring(convert(varchar,GETDATE(),120),0,12)+'16:00:00')
group by MONIDATE) as b
on 1=1
--**part2_end**--
join
--**part3**--
--前一天的日数据(O3_8H要参与计算:AQI,)
(select
--计算AQI
[dbo].[fun_AirQualityIndex]([dbo].[co2iaqi_24h](AVG(CO)),[dbo].[so22iaqi_24h](AVG(SO2)),
[dbo].[no22iaqi_24h](AVG(NO2)),[dbo].[o32iaqi](AVG(O3)),[dbo].[o3_8h2iaqi](AVG(O38H)),
[dbo].[pm252iaqi](AVG(PM25)),[dbo].[pm102iaqi](AVG(PM10))) AQI,
--计算 首要污染物
[dbo].[fun_AQI_FristPollutantDay]([dbo].[co2iaqi_24h](AVG(CO)),[dbo].[so22iaqi_24h](AVG(SO2)),
[dbo].[no22iaqi_24h](AVG(NO2)),[dbo].[o32iaqi](AVG(O3)),[dbo].[o3_8h2iaqi](AVG(O38H)),
[dbo].[pm252iaqi](AVG(PM25)),[dbo].[pm102iaqi](AVG(PM10))) PRIMARYPOLLUTANT,
--空气质量等级
[dbo].[fun_AirAQIdegree]([dbo].[fun_AirQualityIndex]([dbo].[co2iaqi_24h](AVG(CO)),[dbo].[so22iaqi_24h](AVG(SO2)),
[dbo].[no22iaqi_24h](AVG(NO2)),[dbo].[o32iaqi](AVG(O3)),[dbo].[o3_8h2iaqi](AVG(O38H)),
[dbo].[pm252iaqi](AVG(PM25)),[dbo].[pm102iaqi](AVG(PM10)))) AIRLEVEL
from T_MID_AIRQUALITYDAY
where MONIDATE=substring(convert(varchar,DATEADD(DAY,-1,GETDATE()),120),0,12)
group by MONIDATE) as c
on 1=1
--**part3_end**--
join
--**part4**--
--模式预报数据(O3_8H要参与计算:AQI,)
(select md1.AQI_24_model,
SUBSTRING(md1.Poll_24_model,0,LEN(md1.Poll_24_model)) Poll_24_model,
md2.AQI_48_model,
SUBSTRING(md2.Poll_48_model,0,LEN(md2.Poll_48_model)) Poll_48_model,
md3.AQI_72_model,
SUBSTRING(md3.Poll_72_model,0,LEN(md3.Poll_72_model)) Poll_72_model from
--24时
(select m1.AQI AQI_24_model,m1.PRIMARYPOLLUTANT Poll_24_model from
(select
--计算AQI
[dbo].[fun_AirQualityIndex]([dbo].[co2iaqi_24h](AVG(CO)),[dbo].[so22iaqi_24h](AVG(SO2)),
[dbo].[no22iaqi_24h](AVG(NO2)),[dbo].[o32iaqi](AVG(O3)),[dbo].[o3_8h2iaqi](AVG(O38H)),
[dbo].[pm252iaqi](AVG(PM25)),[dbo].[pm102iaqi](AVG(PM10))) AQI,
--计算 首要污染物
[dbo].[fun_AQI_FristPollutantDay]([dbo].[co2iaqi_24h](AVG(CO)),[dbo].[so22iaqi_24h](AVG(SO2)),
[dbo].[no22iaqi_24h](AVG(NO2)),[dbo].[o32iaqi](AVG(O3)),[dbo].[o3_8h2iaqi](AVG(O38H)),
[dbo].[pm252iaqi](AVG(PM25)),[dbo].[pm102iaqi](AVG(PM10))) PRIMARYPOLLUTANT,
DATATYPE
from T_MID_FORECASTSTATISTICS
where MONITORTIME=substring(convert(varchar,GETDATE(),120),0,11) and MODEL='1'
group by MONITORTIME,FORECASTTIME,DATATYPE) as m1 where m1.DATATYPE='24') as md1
join
--48时
(select m2.AQI AQI_48_model,m2.PRIMARYPOLLUTANT Poll_48_model from
(select
--计算AQI
[dbo].[fun_AirQualityIndex]([dbo].[co2iaqi_24h](AVG(CO)),[dbo].[so22iaqi_24h](AVG(SO2)),
[dbo].[no22iaqi_24h](AVG(NO2)),[dbo].[o32iaqi](AVG(O3)),[dbo].[o3_8h2iaqi](AVG(O38H)),
[dbo].[pm252iaqi](AVG(PM25)),[dbo].[pm102iaqi](AVG(PM10))) AQI,
--计算 首要污染物
[dbo].[fun_AQI_FristPollutantDay]([dbo].[co2iaqi_24h](AVG(CO)),[dbo].[so22iaqi_24h](AVG(SO2)),
[dbo].[no22iaqi_24h](AVG(NO2)),[dbo].[o32iaqi](AVG(O3)),[dbo].[o3_8h2iaqi](AVG(O38H)),
[dbo].[pm252iaqi](AVG(PM25)),[dbo].[pm102iaqi](AVG(PM10))) PRIMARYPOLLUTANT,
DATATYPE
from T_MID_FORECASTSTATISTICS
where MONITORTIME=substring(convert(varchar,GETDATE(),120),0,11) and MODEL='1'
group by MONITORTIME,FORECASTTIME,DATATYPE) as m2 where m2.DATATYPE='48') as md2
on 1=1
join
--72时
(select m3.AQI AQI_72_model,m3.PRIMARYPOLLUTANT Poll_72_model from
(select
--计算AQI
[dbo].[fun_AirQualityIndex]([dbo].[co2iaqi_24h](AVG(CO)),[dbo].[so22iaqi_24h](AVG(SO2)),
[dbo].[no22iaqi_24h](AVG(NO2)),[dbo].[o32iaqi](AVG(O3)),[dbo].[o3_8h2iaqi](AVG(O38H)),
[dbo].[pm252iaqi](AVG(PM25)),[dbo].[pm102iaqi](AVG(PM10))) AQI,
--计算 首要污染物
[dbo].[fun_AQI_FristPollutantDay]([dbo].[co2iaqi_24h](AVG(CO)),[dbo].[so22iaqi_24h](AVG(SO2)),
[dbo].[no22iaqi_24h](AVG(NO2)),[dbo].[o32iaqi](AVG(O3)),[dbo].[o3_8h2iaqi](AVG(O38H)),
[dbo].[pm252iaqi](AVG(PM25)),[dbo].[pm102iaqi](AVG(PM10))) PRIMARYPOLLUTANT,
DATATYPE
from T_MID_FORECASTSTATISTICS
where MONITORTIME=substring(convert(varchar,GETDATE(),120),0,11) and MODEL='1'
group by MONITORTIME,FORECASTTIME,DATATYPE) as m3 where m3.DATATYPE='72') as md3
on 1=1
) as m
on 1=1
--**part4_end**--
join
--**part5**--
--气象数据
(
select
MONITORTIME
--实测气象数据
,(select hPa_500 from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=0) h_500--一气象实况h_500
,(select hPa_700 from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=0) h_700--一气象实况h_700
,(select hPa_850 from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=0) h_850--一气象实况h_850
,(select GROUND from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=0) DM--一气象实况DM
,(select WEATHER from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=0) weather--一气象实况weather
,(select WINDSPEED from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=0) wind--一气象实况wind
,(select RELATIVEHUMIDITY from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=0) humidity--一气象实况humidity
,(select DIFFUSIONCONDITIONS from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=0) air--一气象实况air
--,(select hPa_500 from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
--and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=1) h_500_24--一24时
--24气象数据
,(select hPa_500 from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=1) h_500_24--一24时h_500
,(select hPa_700 from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=1) h_700_24--一24时h_700
,(select hPa_850 from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=1) h_850_24--一24时h_850
,(select GROUND from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=1) DM_24--一24时DM
,(select WEATHER from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=1) weather_24--一24时weather
,(select WINDSPEED from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=1) wind_24--一24时wind
,(select RELATIVEHUMIDITY from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=1) humidity_24--一24时humidity
,(select DIFFUSIONCONDITIONS from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=1) air_24--一24时air
--,(select hPa_500 from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
--and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=2) --一48时
--48气象数据
,(select hPa_500 from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=2) h_500_48--一48时h_500
,(select hPa_700 from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=2) h_700_48--一48时h_700
,(select hPa_850 from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=2) h_850_48--一48时h_850
,(select GROUND from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=2) DM_48--一48时DM
,(select WEATHER from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=2) weather_48--一48时weather
,(select WINDSPEED from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=2) wind_48--一48时wind
,(select RELATIVEHUMIDITY from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=2) humidity_48--一48时humidity
,(select DIFFUSIONCONDITIONS from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=2) air_48--一48时air
--,(select hPa_500 from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
--and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=3) h_500_48--一72时
--72气象数据
,(select hPa_500 from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=3) h_500_72--一72时h_500
,(select hPa_700 from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=3) h_700_72--一72时h_700
,(select hPa_850 from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=3) h_850_72--一72时h_850
,(select GROUND from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=3) DM_72--一72时DM
,(select WEATHER from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=3) weather_72--一72时weather
,(select WINDSPEED from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=3) wind_72--一72时wind
,(select RELATIVEHUMIDITY from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=3) humidity_72--一72时humidity
,(select DIFFUSIONCONDITIONS from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=3) air_72--一72时air
from T_MID_METEOROLOGICALOBSERVATIONS t
where MONITORTIME=substring(convert(varchar,GETDATE(),120),0,11) and MODEL='10'
group by MONITORTIME
) as f
on 1=1
--**part5_end**--
join
--**part6**--
--发布数据
(select p.PUBLISHTIME DutyTime,
CASE WHEN p.FORECASTER is null THEN '高级管理员' ELSE p.FORECASTER END
DutyPeople
--p.FORECASTER DutyPeople
,p.AFFECT AFFECT
,convert(varchar,p.AQI1_24,120)+'～'+convert(varchar,AQI2_24,120) AQI_24,p.Poll_24 Poll_24
,convert(varchar,p.AQI1_48,120)+'～'+convert(varchar,AQI2_48,120) AQI_48,p.Poll_48 Poll_48
,convert(varchar,p.AQI1_72,120)+'～'+convert(varchar,AQI2_72,120) AQI_72,p.Poll_72 Poll_72
from
(select
PUBLISHTIME,FORECASTER,AFFECT
,(select AQI1 from T_MID_FORECASTPUBLISH where PUBLISHTIME=t.PUBLISHTIME
and DATEDIFF(DAY,PUBLISHTIME,FORECASTTIME)=1) AQI1_24
,(select AQI2 from T_MID_FORECASTPUBLISH where PUBLISHTIME=t.PUBLISHTIME
and DATEDIFF(DAY,PUBLISHTIME,FORECASTTIME)=1) AQI2_24
,(select PRIMARYPOLLUTANT from T_MID_FORECASTPUBLISH where PUBLISHTIME=t.PUBLISHTIME
and DATEDIFF(DAY,PUBLISHTIME,FORECASTTIME)=1) Poll_24
,(select AQI1 from T_MID_FORECASTPUBLISH where PUBLISHTIME=t.PUBLISHTIME
and DATEDIFF(DAY,PUBLISHTIME,FORECASTTIME)=2) AQI1_48
,(select AQI2 from T_MID_FORECASTPUBLISH where PUBLISHTIME=t.PUBLISHTIME
and DATEDIFF(DAY,PUBLISHTIME,FORECASTTIME)=2) AQI2_48
,(select PRIMARYPOLLUTANT from T_MID_FORECASTPUBLISH where PUBLISHTIME=t.PUBLISHTIME
and DATEDIFF(DAY,PUBLISHTIME,FORECASTTIME)=2) Poll_48
,(select AQI1 from T_MID_FORECASTPUBLISH where PUBLISHTIME=t.PUBLISHTIME
and DATEDIFF(DAY,PUBLISHTIME,FORECASTTIME)=3) AQI1_72
,(select AQI2 from T_MID_FORECASTPUBLISH where PUBLISHTIME=t.PUBLISHTIME
and DATEDIFF(DAY,PUBLISHTIME,FORECASTTIME)=3) AQI2_72
,(select PRIMARYPOLLUTANT from T_MID_FORECASTPUBLISH where PUBLISHTIME=t.PUBLISHTIME
and DATEDIFF(DAY,PUBLISHTIME,FORECASTTIME)=3) Poll_72
from T_MID_FORECASTPUBLISH t
where PUBLISHTIME=substring(convert(varchar,GETDATE(),120),0,11)
group by PUBLISHTIME,FORECASTER,AFFECT
) as p) as pu
on 1=1
--**part6_end**--
where @hour>=(substring(convert(varchar,GETDATE(),120),0,12)+'16:00:00')
END
GO
阅读全文
