统计星期次数的SQL Server存储过程

-- 用于统计指定日期范围内, 星期天到星期六的个数列表
IF   EXISTS  ( SELECT   *   FROM  dbo.sysobjects  WHERE  id  =   OBJECT_ID (N ' [dbo].[usp_GetWeekDayCount] ' )  AND   OBJECTPROPERTY (id, N ' IsProcedure ' )  =   1 )
DROP   PROC   [ dbo ] . [ usp_GetWeekDayCount ]
GO

CREATE   PROC   [ dbo ] . [ usp_GetWeekDayCount ]
     @BeginDate   DATETIME ,  @EndDate   DATETIME
AS
SET  NOCOUNT  ON ;

DECLARE   @WeekDay   TABLE
(
     [ Index ]   TINYINT   NOT   NULL   PRIMARY   KEY ,
     [ Name ]   NVARCHAR ( 20 )  NOT   NULL ,
     [ Count ]      INT
);

DECLARE   @DateFirst   DATETIME ,  @DateLast   DATETIME ;
SELECT   @DateFirst   =   DATEADD (week,  DATEDIFF (week,  0 ,  GETDATE ()),  0 )
    ,  @DateLast   =   DATEADD (week,  DATEDIFF (week,  0 ,  GETDATE ())  +   1 ,  0 );

WHILE   @DateFirst   <   @DateLast
BEGIN
     INSERT   INTO   @WeekDay ( [ Index ] ,  [ Name ] ,  [ Count ] )
     VALUES  ( DATEPART (weekday,  @DateFirst ),  DATENAME (weekday,  @DateFirst ),  0 );

     SET   @DateFirst   =   DATEADD ( day ,  1 ,  @DateFirst );
END ;

SELECT   @DateFirst   =   DATEADD (week,  DATEDIFF (week,  0 ,  @BeginDate )  +   1 ,  0 )
    ,  @DateLast   =   DATEADD (week,  DATEDIFF (week,  0 ,  @EndDate ),  0 );
IF   @DateLast   <   @DateFirst
     UPDATE   @WeekDay
     SET   [ Count ]   =   [ Count ]   +   1
     WHERE   [ Index ]   >=   DATEPART (weekday,  @BeginDate )
         AND   [ Index ]   <=   DATEPART (weekday,  @EndDate );
ELSE
BEGIN
     UPDATE   @WeekDay
     SET   [ Count ]   =   [ Count ]   +   DATEDIFF ( day ,  @DateFirst ,  @DateLast )  /   7 ;

     UPDATE   @WeekDay
     SET   [ Count ]   =   [ Count ]   +   1
     WHERE   [ Index ]   >=   DATEPART (weekday,  @BeginDate );

     UPDATE   @WeekDay
     SET   [ Count ]   =   [ Count ]   +   1
     WHERE   [ Index ]   <=   DATEPART (weekday,  @EndDate );
END ;

-- 与.NET星期索引统一(0-6)
UPDATE   @WeekDay
SET   [ Index ]   =   [ Index ]   -   1 ;

SELECT   *
FROM   @WeekDay ;

SET  NOCOUNT  OFF ;
GO

 

调用示例:

EXEC  usp_GetWeekDayCount  ' 2007-04-01 ' ,  ' 2007-04-30 '

 结果:
Index Name                 Count      
----- -------------------- -----------
0     Sunday               5
1     Monday               5
2     Tuesday              4
3     Wednesday            4
4     Thursday             4
5     Friday               4
6     Saturday             4