【原创源码】(04)：周报表与月报表的存储过程统一实现。

【原创源码】(04)：周报表与月报表的存储过程统一实现。

欢迎大家发表意见（漏洞，性能等）。在博客园社区以外转载，请注明作者和出处。谢谢！

客户好像总是在为难我们，有时候同时需要以下两种报表：

周报表

日期	总数	失败数	百分比
Week1 2006/12/03-2006/12/09	100	80	80.00%
Week2 2006/12/10-2006/12/16	80	40	50.00%
Week3 2006/12/17-2006/12/19	60	20	30.00%
合计	240	140	58.33%

月报表

日期	总数	失败数	百分比
May. 2007	200	96	48.00%
Jun. 2007	200	82	41.00%
Jul. 2007	200	76	38.00%
Aug. 2007	200	78	39.00%
合计	800	332	41.50%

初看上去我应该要写2个存储过程去分别算出2种报表。但是我想偷懒，我认为应该把她分解成3步：
1》计算出日期过滤器。
如周过滤器（输入：2006-12-03  to 2006-12-19）：

SN	DateFirst	DateNext
1	2006-12-03 00:00:00.000	2006-12-09 00:00:00.000
2	2006-12-10 00:00:00.000	2006-12-16 00:00:00.000
3	2006-12-17 00:00:00.000	2006-12-19 00:00:00.000

如月过滤器（输入：2006-10-05  to 2006-12-12）：

SN	DateFirst	DateNext
1	2006-10-05 00:00:00.000	2006-10-31 00:00:00.000
2	2006-11-01 00:00:00.000	2006-11-30 00:00:00.000
3	2006-12-01 00:00:00.000	2006-12-12 00:00:00.000

2》计算出业务数据，包括查询日期，和统计数。
如以下业务数据（演示查询到3条记录）：

QueryDate	Totals	Breaks
2006-12-17 15:00:27.313	1	0
2006-12-25 11:00:12.313	1	1
2006-12-13 14:40:20.390	1	1

3》将上面2个表进行关联，可以分别求出周报表和月报表。

-- @FilterType varchar(50),@BeginDate datetime,@EndDate datetime

CREATE TABLE #Filter(SN int NOT NULL, DateFirst datetime, DateNext datetime)
insert into #Filter exec dbo.usp_GetDateFilter  @FilterType,@BeginDate,@EndDate

IF (@FilterType='weekly')

Begin 
select 'Week'+convert(varchar(10),SN)+' '
+convert(varchar(10),DateFirst,111)
+'-'
+convert(varchar(10),DateNext,111)
AS 'DateFilter'
,ISNULL(SUM(Totals),0) AS 'Totals',ISNULL(SUM(Breaks),0) AS 'Breaks'
from #Container a
right join #Filter b on
datediff(day,a.QueryDate,DateFirst)<=0
 and datediff(day,a.QueryDate,DateNext)>=0 
Group by b.SN,b.DateFirst,b.DateNext
END 

ELSE IF (@FilterType='monthly')

Begin 
select 'Month'+convert(varchar(10),SN)+' '
+DATENAME(m,DateFirst)+'.'+DATENAME(year,DateFirst)
AS 'DateFilter'
,ISNULL(SUM(Totals),0) AS 'Totals',ISNULL(SUM(Breaks),0) AS 'Breaks'
from #Container a
right join #Filter b on
datediff(day,a.QueryDate,DateFirst)<=0
 and datediff(day,a.QueryDate,DateNext)>=0 
Group by b.SN,b.DateFirst,b.DateNext
END 

最后是这篇文章的主角上场了，日期过滤器存储过程：

CREATE    procedure dbo.usp_GetDateFilter 
 @FilterType varchar(50)
,@BeginDate datetime
,@EndDate datetime
AS
/**//*
Creator            :     Clark
Created Date    :    12:06 2006-12-1
Mender            :    Clark
Modified Date    :    12:06 2006-12-1
*/
/**//* Execute Sample
dbo.usp_GetDateFilter 'weekly','2006-10-30','2006-12-5'
dbo.usp_GetDateFilter 'monthly','2006-10-30','2006-12-5'


Testing:
DECLARE @FilterType varchar(50)
DECLARE @BeginDate datetime
DECLARE @EndDate datetime
SET @BeginDate='2006-10-30'
SET @EndDate='2006-12-5'
--SET @FilterType='weekly'
SET @FilterType='monthly'
*/

CREATE TABLE #DateFilter(SN int IDENTITY (1, 1) NOT NULL 
, DateFirst datetime
, DateNext datetime)
--delete from #DateFilter
TRUNCATE TABLE #DateFilter

DECLARE @i int
DECLARE @FirstDate datetime
SET @FirstDate = CONVERT(datetime,
CONVERT(varchar(4),YEAR(@BeginDate))+'-'
+CONVERT(varchar(2),MONTH(@BeginDate))+'-'
+CONVERT(varchar(2),DAY(@BeginDate))
)


IF (@FilterType='weekly')
Begin  

DECLARE @AllDays int
DECLARE @WeekCount int
DECLARE @SurplusDays int

SET @AllDays = DATEDIFF(day,@BeginDate,@EndDate)+1
SET @WeekCount = convert(int,@AllDays/7)
SET @SurplusDays = @AllDays-@WeekCount*7
--print '@AllDays='+convert(varchar(50),@AllDays)
--print '@WeekCount='+convert(varchar(50),@WeekCount)
--print '@SurplusDays='+convert(varchar(50),@SurplusDays)

SET @i = @WeekCount


WHILE (@i >0)
BEGIN
insert into #DateFilter(DateFirst,DateNext) values (@FirstDate,DATEADD(day, 6, @FirstDate))
SET @i = @i -1
SET @FirstDate = DATEADD(day, 7, @FirstDate) 
END

IF (@SurplusDays>0)
BEGIN
insert into #DateFilter(DateFirst,DateNext) values (@FirstDate,DATEADD(day, @SurplusDays-1, @FirstDate))
END

END 

ELSE IF (@FilterType='monthly')
Begin 

DECLARE @MonthCount int
SET @MonthCount = DATEDIFF(month,@BeginDate,@EndDate)+1
SET @i = @MonthCount-2
DECLARE @CurrentMonthFirstDay datetime

DECLARE @CurrentMonthLastDay datetime
DECLARE @NextMonthDay datetime
DECLARE @NextMonthFirstDay datetime

SET @NextMonthDay = DATEADD(month, 1, @BeginDate)
SET @NextMonthFirstDay = CONVERT(datetime,
CONVERT(varchar(4),YEAR(@NextMonthDay))+'-'
+CONVERT(varchar(2),MONTH(@NextMonthDay))+'-'
+'1')
SET @CurrentMonthLastDay = DATEADD(day, -1, @NextMonthFirstDay)

SET @CurrentMonthFirstDay = CONVERT(datetime,
CONVERT(varchar(4),YEAR(@EndDate))+'-'
+CONVERT(varchar(2),MONTH(@EndDate))+'-'
+'1')

--print '@NextMonthDay='+convert(varchar(50),@NextMonthDay)
--print '@NextMonthFirstDay='+convert(varchar(50),@NextMonthFirstDay)
--print '@CurrentMonthLastDay='+convert(varchar(50),@CurrentMonthLastDay)
--print '@CurrentMonthFirstDay='+convert(varchar(50),@CurrentMonthFirstDay)

insert into #DateFilter(DateFirst,DateNext) values (@BeginDate,@CurrentMonthLastDay)

SET @FirstDate = @NextMonthFirstDay
WHILE (@i >0)
BEGIN
insert into #DateFilter(DateFirst,DateNext) values (@FirstDate,DATEADD(month, 1, @FirstDate)-1)
SET @i = @i - 1
SET @FirstDate = DATEADD(month, 1, @FirstDate)
END
 
insert into #DateFilter(DateFirst,DateNext) values (@CurrentMonthFirstDay,@EndDate)

End 

select * from #DateFilter

DROP TABLE #DateFilter

GO

总结分析：这里的周报表中的日期还是“假周”（不一定是真正的星期一到星期日），所以只要修改usp_GetDateFilter是可以支持新的需求变化的（例如：“真周”，星期一到星期日）。


ps: 两个存储过程思路非常简单，作用也简单，高手路过就好：）
如果觉得有错误或者写不好的地方，请告知，谢谢~！


在博客园社区以外转载，必须注明：
作者：Clark Chan
和原文出处：http://clarkchan.cnblogs.com/
否则谢绝转载！