SQL Server collects rainfall data based on conditions

Obtain the hours of the current time in SQL:

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

 

Problem conditions:

If the current time is before 8 hours, the accumulated rainfall after 8 hours of yesterday is used. If the time is after 8 days, the data is obtained after 8 hours of today. The SQL statement is as follows:

 

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) t1where  t.STCD = t1.STCD and t.STTP not in ('pp') and t.ADDVCD like '3410%'

-- Select D for all rainfall sites. stnm, (case when D. stcd = E. stcd then E. DRP else 0 end) as DRP, D. lttd, D. lgtd, D. stcdfrom (select T. stnm, T. lttd, T. lgtd, T. stcd from st_stbprp_ B t where STTP = 'pp 'or stcd like' _______ 4 ') dleft 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 wherfe->=( case when ltrim (datepart (HH, getdate ()> = 8 then convert (varchar (10), getdate (), 120) + ''else convert (varchar (10), getdate ()-1,120) +'' end) group by stcd) TTT on TT. stcd = TTT. stcd2) T1 where T. stcd = t1.stcd) Eon D. stcd = E. stcd; -- select D for all water stations. stnm, (case when D. stcd = E. stcd then E. Z else 0 end) as Z, D. lttd, D. lgtd, D. stcdfrom (select T. stnm, T. lttd, T. lgtd, T. stcd from st_stbprp_ B t where STTP <> 'pp ') dleft 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) Eon D. stcd = E. stcd;

Certificate ---------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

-- Create view v_sk_warninginfoasselect zdt8 as S, tmnow as slasttime, T. stcd as watermark code, stnm as watermark name, County of stlc as, lgtd, lttd, showlevel, wrz, grz, TT. STTP, TT. rvnm, T. msgfrom (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' alert 'when zdt8 between wrz and grz then' superalert: '+ Cast (zdt8-WRZ) as varchar (10 )) when zdt8> grz then 'super guarantee:' + Cast (zdt8-GRZ) as varchar (10) else 'normal' 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 'up to limit 'when zdt8> fsltdz then' ultra limit: '+ Cast (zdt8-FSLTDZ) as varchar (10 )) else 'normal' end as MSG from st_rsvr_d) T, (select stcd, STTP, rvnm from st_stbprp_ B where STTP = 'rr ') ttwhere T. stcd = TT. stcd and MSG <> 'normal'; -- create view v_hd_warninginfoasselect zdt8 as S, tmnow as slasttime, T. stcd as watermark code, stnm as watermark name, County of stlc as, lgtd, lttd, showlevel, wrz, grz, TT. STTP, TT. rvnm, T. msgfrom (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' alert 'when zdt8 between wrz and grz then' superalert: '+ Cast (zdt8-WRZ) as varchar (10 )) when zdt8> grz then 'super guarantee:' + Cast (zdt8-GRZ) as varchar (10) else 'normal' 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 'up to limit 'when zdt8> fsltdz then' ultra limit: '+ Cast (zdt8-FSLTDZ) as varchar (10 )) else 'normal' end as MSG from st_rsvr_d) T, (select stcd, STTP, rvnm from st_stbprp_ B where STTP = 'zz ') ttwhere T. stcd = TT. stcd and MSG <> 'normal ';