触发器(十五)Trigger_Insert_ForeDutyLog

USE [AirDB_LiuZhou]
GO


/****** Object:  Trigger [dbo].[Trigger_Insert_ForeDutyLog]    Script Date: 2017-12-26 10:01:27 ******/
SET ANSI_NULLS ON
GO


SET QUOTED_IDENTIFIER ON
GO






-- =============================================
-- Author: pyy
-- Create date: 2016-10-13
-- Description: 发布时自动保存‘预报值班登记’
-- =============================================
CREATE TRIGGER [dbo].[Trigger_Insert_ForeDutyLog] 
   ON  [dbo].[T_MID_FORECASTPUBLISH]
   AFTER INSERT--,DELETE,UPDATE
AS 
BEGIN
declare @date datetime
--获取当天的日期"yyyy-mm-dd"
set @date=substring(convert(varchar,GETDATE(),120),0,11)
--先删除当天的‘预报值班登记’数据，再重新插入
--删除
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 and MODEL='10') h_500--一气象实况h_500
,(select hPa_700 from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME 
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=0 and MODEL='10') h_700--一气象实况h_700
,(select hPa_850 from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME 
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=0 and MODEL='10') h_850--一气象实况h_850
,(select GROUND from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME 
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=0 and MODEL='10') DM--一气象实况DM
,(select WEATHER from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME 
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=0 and MODEL='10') weather--一气象实况weather
,(select WINDSPEED from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME 
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=0 and MODEL='10') wind--一气象实况wind
,(select RELATIVEHUMIDITY from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME 
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=0 and MODEL='10') humidity--一气象实况humidity
,(select DIFFUSIONCONDITIONS from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME 
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=0 and MODEL='10') 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 and MODEL='10') h_500_24--一24时h_500
,(select hPa_700 from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME 
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=1 and MODEL='10') h_700_24--一24时h_700
,(select hPa_850 from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME 
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=1 and MODEL='10') h_850_24--一24时h_850
,(select GROUND from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME 
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=1 and MODEL='10') DM_24--一24时DM
,(select WEATHER from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME 
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=1 and MODEL='10') weather_24--一24时weather
,(select WINDSPEED from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME 
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=1 and MODEL='10') wind_24--一24时wind
,(select RELATIVEHUMIDITY from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME 
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=1 and MODEL='10') humidity_24--一24时humidity
,(select DIFFUSIONCONDITIONS from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME 
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=1 and MODEL='10') 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 and MODEL='10') h_500_48--一48时h_500
,(select hPa_700 from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME 
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=2 and MODEL='10') h_700_48--一48时h_700
,(select hPa_850 from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME 
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=2 and MODEL='10') h_850_48--一48时h_850
,(select GROUND from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME 
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=2 and MODEL='10') DM_48--一48时DM
,(select WEATHER from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME 
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=2 and MODEL='10') weather_48--一48时weather
,(select WINDSPEED from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME 
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=2 and MODEL='10') wind_48--一48时wind
,(select RELATIVEHUMIDITY from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME 
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=2 and MODEL='10') humidity_48--一48时humidity
,(select DIFFUSIONCONDITIONS from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME 
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=2 and MODEL='10') 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 and MODEL='10') h_500_72--一72时h_500
,(select hPa_700 from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME 
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=3 and MODEL='10') h_700_72--一72时h_700
,(select hPa_850 from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME 
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=3 and MODEL='10') h_850_72--一72时h_850
,(select GROUND from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME 
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=3 and MODEL='10') DM_72--一72时DM
,(select WEATHER from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME 
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=3 and MODEL='10') weather_72--一72时weather
,(select WINDSPEED from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME 
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=3 and MODEL='10') wind_72--一72时wind
,(select RELATIVEHUMIDITY from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME 
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=3 and MODEL='10') humidity_72--一72时humidity
,(select DIFFUSIONCONDITIONS from T_MID_METEOROLOGICALOBSERVATIONS where MONITORTIME=t.MONITORTIME 
and DATEDIFF(DAY,MONITORTIME,FORECASTTIME)=3 and MODEL='10') 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**--












END






GO