--存储过程简单实现：
--id 记录时间 1时，2时，3时......24时 hj
create table #byday(id int identity,dt datetime,hour1 float,hour2 float,hour3 float,hour4 float
    ,hour5 float,hour6 float,hour7 float,hour8 float,hour9 float,hour10 float
    ,hour11 float,hour12 float,hour13 float,hour14 float,hour15 float,hour16 float,hour17 float,hour18 float,hour19 float
    ,hour20 float,hour21 float,hour22 float,hour23 float,hour24 float) 
--年降雨量
--id 年份 1月份 2月份 3月份...... 12月份 hj

create table #byyear(id int identity,year int,month1 float,month2 float,month3 float,month4 float,month5 float,month6 float
    ,month7 float,month8 float,month9 float,month10 float,month11 float,month12 float)


select * from #byday
select * from #byyear
go

create procedure usp_sync_year
(
    @dt datetime
    ,@hour1 float,@hour2 float,@hour3 float,@hour4 float
    ,@hour5 float,@hour6 float,@hour7 float,@hour8 float,@hour9 float,@hour10 float
    ,@hour11 float,@hour12 float,@hour13 float,@hour14 float,@hour15 float,@hour16 float,@hour17 float,@hour18 float,@hour19 float
    ,@hour20 float,@hour21 float,@hour22 float,@hour23 float,@hour24 float
)
as
begin
    insert into #byday(dt,hour1,hour2,hour3,hour4,hour5,hour6,hour7,hour8,hour9,hour10,hour11,hour12
        ,hour13,hour14,hour15,hour16,hour17,hour18,hour19,hour20,hour21,hour22,hour23,hour24)
    select @dt,@hour1,@hour2,@hour3,@hour4,@hour5,@hour6,@hour7,@hour8,@hour9,@hour10,@hour11,@hour12
        ,@hour13,@hour14,@hour15,@hour16,@hour17,@hour18,@hour19,@hour20,@hour21,@hour22,@hour23,@hour24
    
    declare @all_count float
    set @all_count=@hour1+@hour2+@hour3+@hour4+@hour5+@hour6+@hour7+@hour8+@hour9+@hour10+@hour11+@hour12
        +@hour13+@hour14+@hour15+@hour16+@hour17+@hour18+@hour19+@hour20+@hour21+@hour22+@hour23+@hour24
    
    if @all_count =0 
    begin
        return
    end
    declare @year int,@month int
    select @year=YEAR(@dt),@month=MONTH(@dt)
    if not exists(select id from #byyear where [year]=@year)
    begin
        insert into #byyear(year,month1,month2,month3,month4,month5,month6,month7,month8,month9,month10,month11,month12)
        select @year,0,0,0,0,0,0,0,0,0,0,0,0
    end

    declare @sql varchar(1000)
    set @sql='update #byyear set month'+cast(@month as varchar(5))+'=month'+cast(@month as varchar(5))+'+'+cast(@all_count as varchar(5))+' where year='+cast(@year as varchar(5))
    
    exec sp_sqlexec @sql
    
    return
end