SQL动态日期列统计

来源：互联网发布：淘宝一分钱试用在哪里编辑：程序博客网时间：2024/06/04 08:33

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))beginINSERT INTO #dt_date(date) VALUES(@ThisDate)SET @ThisDate=CONVERT(VARCHAR(10),dateadd(day,1,cast(@ThisDate as datetime)),120)endSELECT * FROM #dt_dateDROP TABLE #dt_date

2.效果演示（起始日期为：2015-01，结束日期为：2015-04，时间部分全部为动态生成。无限跨年份）

3.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)ASSET @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 @ThisDateSET @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)endSET @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<>''BEGINSET @StrSQL=@StrSQL+' WHERE 1=1 '+@strWhereEND--PRINT(@StrSQL)EXEC(@StrSQL)

0 0