SQL Server雨情處理自動補資料

在水利水雨情遙測資料的處理當中，一個ST_PPTN_R表中記錄著各個站點在有降雨的情況下整點的累計雨量資料，而在沒有降雨的情況下，是不往該表中寫資料的。

在Android開發中，利用achartengine來繪製雨量柱狀圖的時候，要繪製時報、日報、月報，可以使用下面的語句進行

--時報
SELECT [TM],[DRP] FROM [ST_PPTN_R] WHERE STCD='62927600' AND TM>='2014-08-11 09:00' AND TM<='2014-08-12 08:00'  ORDER BY [TM];

--日報
SELECT convert(varchar(10),tm,120) [TM],sum([DRP]) drp FROM [ST_PPTN_R] WHERE STCD='62927600' AND TM>='2014-08-01' AND TM<='2014-08-31' group BY convert(varchar(10),tm,120);

--月報
SELECT convert(varchar(7),tm,120) [TM],sum([DRP]) drp FROM [ST_PPTN_R] WHERE STCD='62927600' AND TM>='2014-01-01' AND TM<='2014-12-31'  group BY convert(varchar(7),tm,120);

截圖如下：

這樣就存在一個問題，就是需要補時間，以下提供一種利用C#程式碼補非降雨時間：

if (context.Request.QueryString["cls"].ToString() == "sb")
{
     dt2 = FillNewDataTable(dm.ExecuteDataTable(ssql, ct, sqlP), "sb");
}
if (context.Request.QueryString["cls"].ToString() == "rb")
{
     dt2 = FillNewDataTable(dm.ExecuteDataTable(ssql, ct, sqlP), "rb");
}
if (context.Request.QueryString["cls"].ToString() == "yb")
{
     dt2 = FillNewDataTable(dm.ExecuteDataTable(ssql, ct, sqlP), "yb");
}

...

#region  對缺少的日期進行自動補值 
    
    public DataTable FillNewDataTable(DataTable dt,string type)
    {
        DataTable datatable = new DataTable();
        DataColumn dc = null;
        dc = datatable.Columns.Add("TM", Type.GetType("System.DateTime"));
        dc = datatable.Columns.Add("DRP", Type.GetType("System.Double"));
        switch (type)
        {
            case "sb":
                for (DateTime d = BEGIN; d <= END; d=d.AddHours(1))
                {
                    DataRow[] drv = dt.Select(" TM='" + d + "'");
                    if (drv.Length == 0)
                    {
                        DataRow dr = datatable.NewRow();
                        dr["TM"] = d;
                        dr["DRP"] = 0.0;
                        datatable.Rows.Add(dr);
                    }
                    else if (drv.Length == 1)
                    {
                        DataRow dr = datatable.NewRow();
                        dr["TM"] = d;
                        foreach (DataRow row in drv)
                        {
                            dr["DRP"] =row[1];
                        }
                        datatable.Rows.Add(dr); 
                    }
                }
                break;
            case "rb":
                for (DateTime d = BEGIN; d <= END; d = d.AddDays(1))
                {
                    DataRow[] drv = dt.Select(" TM='" + d.ToString("yyy-MM-dd") + "'");
                    if (drv.Length == 0)
                    {
                        DataRow dr = datatable.NewRow();
                        dr["TM"] = d;
                        dr["DRP"] = 0.0;
                        datatable.Rows.Add(dr);
                    }
                    else if (drv.Length == 1)
                    {
                        DataRow dr = datatable.NewRow();
                        dr["TM"] = d;
                        foreach (DataRow row in drv)
                        {
                            dr["DRP"] = row[1];
                        }
                        datatable.Rows.Add(dr);
                    }
                }
                break;
            case "yb":
                for (DateTime d = BEGIN; d <= END; d = d.AddMonths(1))
                {
                    DataRow[] drv = dt.Select(" TM='" + d.ToString("yyyy-MM") + "'");
                    if (drv.Length == 0)
                    {
                        DataRow dr = datatable.NewRow();
                        dr["TM"] = d;
                        dr["DRP"] = 0.0;
                        datatable.Rows.Add(dr);
                    }
                    else if (drv.Length == 1)
                    {
                        DataRow dr = datatable.NewRow();
                        dr["TM"] = d;
                        foreach (DataRow row in drv)
                        {
                            dr["DRP"] = row[1];
                        }
                        datatable.Rows.Add(dr);
                    }
                }
                break; 
        }
        return datatable;
    }
    
    #endregion
...