环比 月、年

string fieldName = ReportDimension.GetFieldDesc4Equipment(fieldID);
string monthOrYear = (endYear==0)?“MONTH”:“YEAR”;
string monthOrYearLeftJoinStr = (endYear == 0) ? " AND DATEPART(YEAR,r1.RequestDate) = DATEPART(YEAR ,r2.RequestDate) " : “”;
string monthOrYearWhereStr = (endYear == 0) ? " AND DATEPART(YEAR,r1.RequestDate)= @StartYear " : " AND DATEPART(YEAR ,r1.RequestDate)>=@StartYear AND DATEPART(YEAR,r1.RequestDate)<=@EndYear ";

        sqlStr = string.Format("SELECT {0}, DATEPART({1} ,r1.RequestDate) AS monthOrYear,COUNT(DISTINCT r1.ID) AS now,COUNT(DISTINCT r2.ID) AS pre "+
                              " FROM tblRequest r1 "+

                              " LEFT JOIN jctRequestEqpt re ON re.RequestID =r1.ID  "+
                              " LEFT JOIN tblEquipment e ON e.ID =re.EquipmentID "+
                              " LEFT JOIN ( "+

                              " SELECT sr.ID ID,sr.RequestDate,sr.RequestType,{0} " +
                              " FROM tblEquipment e  "+
                              " LEFT JOIN jctRequestEqpt sre ON sre.EquipmentID = e.ID "+
                              " LEFT JOIN tblRequest sr ON sr.ID =sre.RequestID "+

                              " ) r2 ON r1.RequestType =r2.RequestType  "+
                              " AND DATEPART({1},r1.RequestDate) = DATEPART({1} ,r2.RequestDate)+1  {2}" +
                              " AND r2{4} = {0} " +

                              " WHERE r1.RequestType=@RequestType {3} AND re.RequestID IS NOT NULL AND re.EquipmentID IS NOT NULL "+
                              " GROUP BY DATEPART({1} ,r1.RequestDate) ,{0}",
                              fieldName,
                              monthOrYear,
                              monthOrYearLeftJoinStr,
                              monthOrYearWhereStr,
                              fieldName.Substring(fieldName.IndexOf(".")));
        using (SqlCommand command = ConnectionUtil.GetCommand(sqlStr))
        {
            command.Parameters.Add("@RequestType", SqlDbType.Int).Value = (type == RequestInfo.RequestTypes.Recall) ? RequestInfo.RequestTypes.Inspection : type;
            command.Parameters.Add("@StartYear", SqlDbType.Int).Value = startYear;
            if (endYear != 0)
                command.Parameters.Add("@EndrtYear", SqlDbType.Int).Value = endYear;

            return GetDataTable(command);
        }