A304-農業水資源智慧決策支援平台

2024-08-01 取得近十年網格平均降雨量料¶

-- =============================================
-- Author:RECO
-- Create date: 2024/08/01
-- Description: 取得近十年網格的降雨量資料
-- exec sp_GetTenYearGridInfo '05-30', '07-30'
-- =============================================
Create Procedure sp_GetTenYearGridInfo
(
@StartDate varchar(5),
@EndDate varchar(5)
)
as
Begin

    -- 設定要統計的啟始日期
    if (@StartDate='' or Len(@StartDate)=0)
    Begin
        Set @StartDate='01-01'
    End

    -- 設定要統計的結束日期
    if (@EndDate='' or Len(@EndDate)=0)
    Begin
        Set @EndDate='12-31'
    End

    -- 取得網格資料
    ;With Source
    as
    (
        select distinct GridNumber, DataYear, SUM(case when Rain<0 then 0 else Rain end) as [DataValue]
        from tbl_GridCumulativeDailyRainfallOneToThree a
        where DataYear >=2014 and DataYear<=2023 and DataDate between @StartDate and @EndDate and GridSize=3
        group by GridNumber, DataYear
        --order by GridNumber, DataYear
    )
    select a.GridNumber, b.Latitude, b.Longitude, AVG(DataValue) as DataValue
    from Source a inner join tbl_GridInfo b on b.GridSize=3 and b.GridNumber=a.GridNumber
    group by a.GridNumber, b.Latitude, b.Longitude
    order by a.GridNumber

End