SQL Server 按照條件統計雨量資料

SQL中獲取當前時間的小時數:

select ltrim(datepart(hh,getdate()));

 

問題條件：

如果當前時間在8時之前，則取昨日8時後的累積雨量，如果在8日之後，則從今日8時之後取資料，SQL語句如下：

 

select t.STNM,t1.DRP,t.LTTD,t.LGTD,t1.STCD from
(
    select STNM,LTTD,LGTD,STCD from ST_STBPRP_B
) t
,
(
      select distinct(STCD) as STCD,ttt.DRP as DRP from ST_PPTN_R tt
      left join 
      (
           select STCD as STCD2,sum(DRP) as DRP from ST_PPTN_R where
           TM>=
            (
                case when ltrim(datepart(hh,getdate()))>=8 then convert(varchar(10),getdate(),120)+' 08:00' else convert(varchar(10),getdate()-1,120)+' 08:00' end
            ) 
            group by STCD
      ) ttt 
      on tt.STCD=ttt.STCD2 
) t1 
where t.STCD=t1.STCD;

 

select t.STNM,t1.Z,t.LTTD,t.LGTD,t1.STCD from
				(
    					select STNM,LTTD,LGTD,STCD,ADDVCD,STTP from ST_STBPRP_B 
				)t
				,
				(
     					select distinct(STCD) as STCD,t3.Z from 
     					(
         					select STCD,TM,Z from ST_RIVER_R
         					union 
         					select STCD,TM,RZ as Z from ST_RSVR_R
     					) t2 
     					left join
     					(
       						  select tt.STCD as STCD2, avg(Z) as Z from 
         					 (
            						 select STCD,TM,Z from ST_RIVER_R
             						 union 
             						 select STCD,TM,RZ as Z from ST_RSVR_R
         					) tt
         					where  tt.TM>=
                                                (
                        				case when ltrim(datepart(hh,getdate()))>=8 then convert(varchar(10),getdate(),120)+' 08:00' else convert(varchar(10),getdate()-1,120)+' 08:00' end  
                                                )
                                                group by tt.STCD
      					) t3
      					on t2.STCD = t3.STCD2
				) t1
				where  t.STCD = t1.STCD and t.STTP not in ('pp') and t.ADDVCD like '3410%'

--所有雨量站點
select d.STNM,
(
 	case when d.STCD=e.STCD then e.DRP else 0 end
) as DRP,
d.LTTD,d.LGTD,d.STCD
from
(
	select t.STNM,t.LTTD,t.LGTD,t.STCD from ST_STBPRP_B t where STTP='PP' or STCD like '_______4'
) d
left join
(
	select t.STNM,ISNULL(t1.DRP,0) DRP,t.LTTD,t.LGTD,t1.STCD from
	(
   		 select STNM,LTTD,LGTD,STCD from ST_STBPRP_B where STTP='PP' or STCD like '_______4'
	) t
	,
	(
      		select distinct(STCD) as STCD,ttt.DRP as DRP from ST_PPTN_R tt
      		left join 
      		(
			select STCD as STCD2,sum(DRP) as DRP from ST_PPTN_R where
			TM>=
			(
   				case when ltrim(datepart(hh,getdate()))>=8 then convert(varchar(10),getdate(),120)+' 08:00' else convert(varchar(10),getdate()-1,120)+' 08:00' end
			) 
			group by STCD
      		) ttt 
      		on tt.STCD=ttt.STCD2 
	) t1 
	where t.STCD=t1.STCD
) e
on d.STCD=e.STCD;

--所有水位站


select d.STNM,
(
  	case when d.STCD=e.STCD then e.Z else 0 end
) as Z,
d.LTTD,d.LGTD,d.STCD
from
(
 	select t.STNM,t.LTTD,t.LGTD,t.STCD from ST_STBPRP_B t where STTP<>'PP'
) d
left join
(
 	select t.STNM,t1.Z,t.LTTD,t.LGTD,t1.STCD from
 	(
      		select STNM,LTTD,LGTD,STCD from ST_STBPRP_B 
 	)t
 	,
 	(
    	   	select * from 
  	(  
   		select STCD,TM,Z from ST_RIVER_R
    		union 
      	         select STCD,TM,RZ as Z from ST_RSVR_R  
  	) t
  	where t.TM in   
 	(  
     	 	select MAX(TM) from 
      		(
            		select STCD,TM,Z from ST_RIVER_R
       			union 
       			select STCD,TM,RZ as Z from ST_RSVR_R  
      		) t1
     		where t.STCD=t1.STCD  
 	 )  
 ) t1
 where  t.STCD = t1.STCD
) e
on d.STCD=e.STCD;

---------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

--報警水庫資料
create view v_SK_WarningInfo
as
select zdt8 as s,tmnow as slasttime,t.STCD as 水位站編碼,STNM as 水位站名稱,STLC as 所屬縣,LGTD,LTTD,ShowLevel,WRZ,GRZ,tt.STTP,tt.RVNM,t.Msg
from
(
     select STCD,STNM,LGTD,LTTD,STLC,zdt8,tmnow,ShowLevel,IsNull(WRZ,0) as WRZ,IsNull(GRZ,0) as GRZ,case when WRZ-zdt8=0 then '達警戒' when zdt8 between WRZ and GRZ then '超警戒:'+cast((zdt8-WRZ) as varchar(10))  when zdt8>GRZ then '超保證:'+cast((zdt8-GRZ) as varchar(10)) else '正常' end as Msg from ST_RIVER_D
     union
    select STCD,STNM,LGTD,LTTD,STLC,zdt8,tmnow,ShowLevel,IsNull(FSLTDZ,0) as WRZ,0 as GRZ,case when FSLTDZ-zdt8=0 then '達汛限'  when zdt8>FSLTDZ then '超汛限:'+cast((zdt8-FSLTDZ) as varchar(10)) else '正常' end as Msg from ST_RSVR_D
) t,
(
    select STCD,STTP,RVNM from ST_STBPRP_B where STTP='RR'
) tt
where t.STCD=tt.STCD and Msg<>'正常';


--報警河道資料
create view v_HD_WarningInfo
as
select zdt8 as s,tmnow as slasttime,t.STCD as 水位站編碼,STNM as 水位站名稱,STLC as 所屬縣,LGTD,LTTD,ShowLevel,WRZ,GRZ,tt.STTP,tt.RVNM,t.Msg
from
(
     select STCD,STNM,LGTD,LTTD,STLC,zdt8,tmnow,ShowLevel,IsNull(WRZ,0) as WRZ,IsNull(GRZ,0) as GRZ,case when WRZ-zdt8=0 then '達警戒' when zdt8 between WRZ and GRZ then '超警戒:'+cast((zdt8-WRZ) as varchar(10))  when zdt8>GRZ then '超保證:'+cast((zdt8-GRZ) as varchar(10)) else '正常' end as Msg from ST_RIVER_D
     union
    select STCD,STNM,LGTD,LTTD,STLC,zdt8,tmnow,ShowLevel,IsNull(FSLTDZ,0) as WRZ,0 as GRZ,case when FSLTDZ-zdt8=0 then '達汛限'  when zdt8>FSLTDZ then '超汛限:'+cast((zdt8-FSLTDZ) as varchar(10)) else '正常' end as Msg from ST_RSVR_D
) t,
(
    select STCD,STTP,RVNM from ST_STBPRP_B where STTP='ZZ'
) tt
where t.STCD=tt.STCD and Msg<>'正常';