1.先了解下原理(看不懂可以,拿到数据库执行,看效果。这个是循环到 - 日,下面的实例是循环到 - 月)
/*
输入2015-07-05,输出2015-07-01 至 2015-07-05
*/
CREATE TABLE #dt_date(date VARCHAR(10))
DECLARE @ThisDate VARCHAR(10),@EndDate VARCHAR(10)
SET @EndDate='2015-07-05'
SET @ThisDate=CONVERT(VARCHAR(7),@EndDate)+'-01'
while(cast(@ThisDate as datetime)<=cast(@EndDate as datetime))
begin
INSERT INTO #dt_date(date) VALUES(@ThisDate)
SET @ThisDate=CONVERT(VARCHAR(10),dateadd(day,1,cast(@ThisDate as datetime)),120)
end
SELECT * FROM #dt_date
DROP TABLE #dt_date
2.效果演示(起始日期为:2015-01,结束日期为:2015-04,时间部分全部为动态生成。
无限跨年份)
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiI0gTMx81dsQWZ4lmZf1GLlpXazVmcvwFciV2dsQXYtJ3bm9CX9s2RkBnVHFmb1clWvB3MaVnRtp1XlBXe0xCMy81dvRWYoNHLwEzX5xCMx8FesU2cfdGLwMzX0xiRGZkRGZ0Xy9GbvNGLpZTY1EmMZVDUSFTU4VFRR9Fd4VGdsYTMfVmepNHLrJXYtJXZ0F2dvwVZnFWbp1zczV2YvJHctM3cv1Ce-cmbw5SN0gjM3ITYhVTNxYTOkRDZyYzX5ADN1ETM4EzLcdDMyIDMy8CXn9Gbi9CXzV2Zh1WavwVbvNmLvR3YxUjLyM3Lc9CX6MHc0RHaiojIsJye.png)
/*
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)