统计油耗比Sql server

最新推荐文章于 2022-10-18 16:09:23 发布

太阳的后裔

最新推荐文章于 2022-10-18 16:09:23 发布

阅读量221

点赞数

分类专栏： Sql server

本文链接：https://blog.csdn.net/weixin_43942765/article/details/105503154

版权

Sql server 专栏收录该内容

24 篇文章 1 订阅

订阅专栏

根据时间来获得统计报表是很多公司都会用到的技术，如何实现呢看下面的Sql语句吧

/// <summary>
        /// 油耗统计
        /// </summary>
        /// <param name="pIndex"></param>
        /// <param name="pNum"></param>
        /// <param name="entity"></param>
        /// <returns></returns>
        public Hashtable QueryOilCostReport(int pIndex, int pNum, TableEntity entity)
        {
            SystemManager system = new SystemManager();
            List<TableEntity> result = new List<TableEntity>();
            Hashtable resHt = new Hashtable();
            try
            {
                string strSql = @" SELECT TOP " + pNum + " *  FROM (select DISTINCT ROW_NUMBER() OVER (ORDER BY b.TruckNum DESC) AS RowNumber,a.OilID,b.TruckNum,b.DepName,b.DepID,b.DepCode,ISNULL(c.Spec,0) as Spec,d.ParentID,SUM(a.AddOil) ";
                strSql += " as AddOil,SUM(a.AddMileage) as AddMileage,SUM(a.ShopMoney) ";
                strSql += " as ShopMoney from TableA as a left join TableB  as b on a.OilID=b.ID left join TableC as c on b.TruckNum=c.CarNum left join TableD as d on b.DepID=d.ID where (1=1) ";
                //日期范围
                if ((entity.StartDate.ToString("yyyy-MM-dd") != "0001-01-01" && entity.StartDate.ToString("yyyy-MM-dd") != "1900-01-01"))
                {
                    strSql += " and a.OP_DATE>='" + entity.StartDate.ToString("yyyy-MM-dd") + "'";
                }
                if ((entity.EndDate.ToString("yyyy-MM-dd") != "0001-01-01" && entity.EndDate.ToString("yyyy-MM-dd") != "1900-01-01"))
                {
                    strSql += " and a.OP_DATE<'" + entity.EndDate.AddDays(1).ToString("yyyy-MM-dd") + "'";
                }
                if (!string.IsNullOrEmpty(entity.DepCode)) { strSql += " and b.DepCode like '" + entity.DepCode + "%'"; }
                if (!string.IsNullOrEmpty(entity.TruckNum)) { strSql += "and b.TruckNum like '%" + entity.TruckNum + "%'"; }
                if (!entity.Spec.Equals(0)) { strSql += " and c.Spec=" + entity.Spec; }
                strSql += " and a.RechargeType=1 group by a.OilID,b.TruckNum,b.DepName,b.DepID,b.DepCode,c.Spec,d.ParentID) f  WHERE RowNumber > (" + pNum + "* (" + pIndex + "-1))";

                using (DbCommand command = conn.GetSqlStringCommond(strSql))
                {
                    using (DataTable dt = conn.ExecuteDataTable(command))
                    {
                        foreach (DataRow idr in dt.Rows)
                        {
                            TableEntity orga = system.QueryOrganizeByID(new TableEntity { ID = Convert.ToInt32(idr["ParentID"]) });
                            result.Add(new TableEntity
                            {
                                OilID = Convert.ToInt32(idr["OilID"]),
                                ShopMoney = Convert.ToDecimal(idr["ShopMoney"]),
                                TruckNum = Convert.ToString(idr["TruckNum"]),
                                AddMileage = Convert.ToDecimal(idr["AddMileage"]),
                                AddOil = Convert.ToDecimal(idr["AddOil"]),
                                Name = Convert.ToString(idr["DepName"]),
                                ParentName = orga.Name,
                                Spec = Convert.ToDecimal(idr["Spec"]),
                                OilWear = Convert.ToDecimal(idr["AddMileage"]).Equals(0) ? 0 : decimal.Round(Convert.ToDecimal((Convert.ToDecimal(idr["AddOil"]) * 100) / Convert.ToDecimal(idr["AddMileage"])), 2)
                            });
                        }
                    }
                }
                resHt["rows"] = result;
                string strCount = @" SELECT distinct a.OilID from TableA as a left join TableB as b on a.OilID=b.ID left join TableC as c on b.TruckNum=c.CarNum where (1=1) ";
                //日期范围
                if ((entity.StartDate.ToString("yyyy-MM-dd") != "0001-01-01" && entity.StartDate.ToString("yyyy-MM-dd") != "1900-01-01"))
                {
                    strCount += " and a.OP_DATE>='" + entity.StartDate.ToString("yyyy-MM-dd") + "'";
                }
                if ((entity.EndDate.ToString("yyyy-MM-dd") != "0001-01-01" && entity.EndDate.ToString("yyyy-MM-dd") != "1900-01-01"))
                {
                    strCount += " and a.OP_DATE<'" + entity.EndDate.AddDays(1).ToString("yyyy-MM-dd") + "'";
                }
                if (!string.IsNullOrEmpty(entity.DepCode)) { strCount += " and b.DepCode like '" + entity.DepCode + "%'"; }
                if (!string.IsNullOrEmpty(entity.TruckNum)) { strCount += "and b.TruckNum like '%" + entity.TruckNum + "%'"; }
                if (!entity.Spec.Equals(0)) { strCount += " and c.Spec=" + entity.Spec; }
                strCount += " and a.RechargeType=1 group by a.OilID";
                using (DbCommand cmd = conn.GetSqlStringCommond(strCount))
                {
                    using (DataTable idrCount = conn.ExecuteDataTable(cmd))
                    {
                        if (idrCount.Rows.Count > 0)
                        {
                            resHt["total"] = idrCount.Rows.Count;
                        }
                    }
                }
            }
            catch (ApplicationException ex) { throw new ApplicationException(ex.Message); }
            return resHt;
        }