SQL:创建某一时间段内的周末日期表以及特殊处理日期表

   近期有个功能需要判断某个给定日内（如 2012-01-06到2012-07-31）有多少个工作日，其中需要用到一个周末日期表，此表列出给定日期段内的所有属于周末的日期。

于是创建如下: 

DROP TABLE #calendar 

CREATE TABLE #calendar (calendardate DATETIME) 

DECLARE @startdate DATETIME
DECLARE @enddate DATETIME

SET @startdate='2012-01-01' 
SET @enddate='2012-07-31' 

WHILE @startdate <= @enddate
 BEGIN
   INSERT INTO #calendar
   ( calendardate)
   SELECT  @startdate
   
   SET @startdate = dateadd (DD, 1, @startdate)
 END

SELECT *  FROM #calendar

 SELECT CALENDARDATE,datename(dw,calendardate) AS DAYNAME 
  ,CASE WHEN datename(dw,calendardate)='Saturday'      THEN dateadd(mi,390,calendardate) 
        WHEN datename(dw,calendardate)='Monday'        THEN dateadd(ss,23399,calendardate) 
        ELSE   calendardate 
   END  AS DELwithWEEKEND
 FROM  #calendar 
 WHERE  datename(dw,calendardate) in ('Saturday','Monday') 
 ORDER BY CALENDARDATE
 

 

还有另一个类似的解决方案，我们创建一个表，此表把给定日期段的每一天都归入某一类(eg:holiday, workday, EOMProcessdate....)如下图:

 

创建步骤如下:
1、创建一个临时表

create table #Corporate_Calendar (
Dateid int identity(1,1),
Date datetime, 
Holiday bit default 0, 
Workday bit default 0, 
EOMProcessdate bit default 0,
EOWProcessdate bit default 0,
SpecialProcessdate bit default 0,
Datestamp datetime default getdate())
go

 

2、在此临时表内填充所有日期。
你需要指定开始的日期以后此后跨度的天数，如此例，我们从2012年1月1号开始，往后创建365天，即2012年一年的数据。

declare @n int
declare @maxn int
declare @begindate datetime
set @n =1
set @maxn=365 -- 日期表中需要创建的天数
set @begindate =convert(datetime,'01/01/2012')
  --日期表内的第一天
set @begindate =@begindate -1

while @n <= @maxn
begin
insert into #Corporate_Calendar(date) select @begindate+@n
set @n=@n+1
end

select * from #Corporate_Calendar

 

3、把日期表内的周末日期和工作日期分别标识出来

update #Corporate_Calendar 
set holiday=1 where datename(dw,date) in ('Saturday','Sunday')
go
update #Corporate_Calendar 
set workday=1 where holiday=0
go

 

4、分别标识出每周最后一天，每月最后一天以及其它特殊处理日标识。如此例，我们标识2012-03-11是某周最后一天。

update #Corporate_Calendar 
set EOWProcessdate =1 where date ='2012-03-11'

 

5、标识出特殊的节假日，如此处我们标识出圣诞节和复活节

update #Corporate_Calendar 
set holiday=1 where date = '12/25/2012' -- Xmas day
go
update #Corporate_Calendar 
set holiday=1 where date = '3/25/2012' -- Easter
go

 
 6、如果我们把此临时表创建到数据库的正常数据表如
内，我们可以通过创建一个Function来使用此表，判断某一天是否在我们需要特殊关照的某类日期内。

create function dbo.udf_isProcessDate (@date datetime, @Type varchar(10))
returns bit
begin
declare @x bit
set @x=NULL
If @type='Holiday' 
select @x = holiday from  dbo.Corporate_Calendar 
    where convert(varchar(10),date,112) =convert(varchar(10),@date,112)
If @type='Workday' 
select @x = WorkDay from dbo.Corporate_Calendar 
    where convert(varchar(10),date,112) =convert(varchar(10),@date,112)
If @type='EOM' 
select @x = EOMProcessdate from dbo.Corporate_Calendar 
    where convert(varchar(10),date,112) =convert(varchar(10),@date,112)
If @type='EOW' 
select @x = EOWProcessdate from dbo.Corporate_Calendar 
    where convert(varchar(10),date,112) =convert(varchar(10),@date,112)
If @type='Special' 
select @x = SpecialProcessdate from dbo.Corporate_Calendar 
    where convert(varchar(10),date,112) =convert(varchar(10),@date,112)
return @x
end
go

使用方法

--select dbo.udf_isProcessDate(getdate(),'WorkDay')