--
用于统计指定日期范围内, 星期天到星期六的个数列表
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