统计星期次数的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