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