SQL Server雨情处理自动补数据

最新推荐文章于 2022-05-26 16:51:30 发布

暖枫无敌

最新推荐文章于 2022-05-26 16:51:30 发布

阅读量1.7k

点赞数

分类专栏： C#.NET编程

本文链接：https://blog.csdn.net/zdw_wym/article/details/38535937

版权

C#.NET编程专栏收录该内容

219 篇文章 2 订阅

订阅专栏

在水利水雨情遥测数据的处理当中，一个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
...