SQL动态日期列生成并统计

/* EXEC usp_CRM_PerformanceReport '2015-01','2016-3',' AND BusinessTypeID=2' */ ALTER PROC usp_CRM_PerformanceReport @ThisDate VARCHAR(10), @EndDate VARCHAR(10), @strWhere NVARCHAR(4000) AS SET @ThisDate=@ThisDate+'-01' SET @EndDate=@EndDate+'-01' DECLARE @StrSQL nvarchar(max) SET @StrSQL=' ;with dt_pager AS( select vw_p.CustomerID, vw_p.CustomerName,vw_p.BusinessTypeName,vw_p.BusinessTypeID,vw_p.PerformanceID,vw_p.Device,vw_p.District, vw_p.UseBeginTime,vw_p.UseEndTime,vw_p.Pay,vw_p.PayName,vw_p.DistrictName,vw_p.FillManName,vw_p.DeductAmount,vw_p.LoadPhone ,tbl_m.MacthDate ' while(cast(@ThisDate as datetime)<=cast(@EndDate as datetime)) begin --SELECT @ThisDate SET @StrSQL=@StrSQL+',(CASE WHEN tbl_m.MacthDate=CONVERT(DATETIME,'''+@ThisDate+''') THEN ''<label style=color:red;>已缴</label>'' ELSE ''<label style="color: #CDCDCD;">未缴</label>'' END) AS ['+CONVERT(NVARCHAR(7),@ThisDate)+']' SET @ThisDate=CONVERT(VARCHAR(10),dateadd(month,1,cast(@ThisDate as datetime)),120) end SET @StrSQL=@StrSQL+' from vw_rtCRM_Performance AS vw_p left join tbl_rtCRM_MatchData AS tbl_m on vw_p.PerformanceID=tbl_m.PerformanceID ) SELECT * FROM dt_pager ' IF @strWhere<>'' BEGIN SET @StrSQL=@StrSQL+' WHERE 1=1 '+@strWhere END --PRINT(@StrSQL) EXEC(@StrSQL)