--
用于统计指定日期范围内, 星期天到星期六的个数列表
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
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
结果:
Index Name Count
----- -------------------- -----------
0 Sunday 5
1 Monday 5
2 Tuesday 4
3 Wednesday 4
4 Thursday 4
5 Friday 4
6 Saturday 4