--@year 年,@H基月始日期,@B基月末日期 --@g:y 年 ,m 月,n 周末数(以星期六为周末来算) CREATE FUNCTION getAS(@year INT,@H INT ,@B INT ) RETURNS @g TABLE(y INT, m INT,n INT) BEGIN DECLARE @t TABLE(id INT,b SMALLDATETIME) DECLARE @a TABLE(id INT IDENTITY(0,1),b INT) INSERT @a SELECT TOP 400 0 FROM syscolumns s
INSERT @t SELECT id, DATEADD(DAY,id,ltrim(@year)+'-01-'+LTRIM(@H)) b FROM @a WHERE DATEADD(DAY,id,ltrim(@year)+'-01-'+LTRIM(@H))<cast(LTRIM(@year+1)+'-01-'+LTRIM(@H) AS SMALLDATETIME)
INSERT @g SELECT @year,m,COUNT(1) FROM ( SELECT b, id, CASE WHEN DAY(b)<@H THEN CASE WHEN MONTH(b)-1=0 THEN 12 ELSE MONTH(b)-1 END ELSE MONTH(b) end m FROM @t )aa WHERE DATEPART(weekday,b)=7 GROUP BY m,DATEPART(weekday,b) RETURN END GO SELECT * FROM dbo.getas(2009,21,20)--result /*y m n ----------- ----------- ----------- 2009 1 4 2009 2 4 2009 3 5 2009 4 4 2009 5 5 2009 6 4 2009 7 4 2009 8 5 2009 9 4 2009 10 4 2009 11 5 2009 12 4(所影响的行数为 12 行)*/
--@g:y 年 ,m 月,n 周末数(以星期六为周末来算)
CREATE FUNCTION getAS(@year INT,@H INT ,@B INT )
RETURNS @g TABLE(y INT, m INT,n INT)
BEGIN
DECLARE @t TABLE(id INT,b SMALLDATETIME)
DECLARE @a TABLE(id INT IDENTITY(0,1),b INT)
INSERT @a SELECT TOP 400 0 FROM syscolumns s
INSERT @t SELECT id, DATEADD(DAY,id,ltrim(@year)+'-01-'+LTRIM(@H)) b FROM @a
WHERE DATEADD(DAY,id,ltrim(@year)+'-01-'+LTRIM(@H))<cast(LTRIM(@year+1)+'-01-'+LTRIM(@H) AS SMALLDATETIME)
INSERT @g
SELECT @year,m,COUNT(1) FROM
(
SELECT b, id,
CASE WHEN DAY(b)<@H THEN
CASE WHEN MONTH(b)-1=0 THEN 12 ELSE MONTH(b)-1 END
ELSE MONTH(b) end m FROM @t
)aa
WHERE DATEPART(weekday,b)=7
GROUP BY m,DATEPART(weekday,b)
RETURN
END
GO SELECT * FROM dbo.getas(2009,21,20)--result
/*y m n
----------- ----------- -----------
2009 1 4
2009 2 4
2009 3 5
2009 4 4
2009 5 5
2009 6 4
2009 7 4
2009 8 5
2009 9 4
2009 10 4
2009 11 5
2009 12 4(所影响的行数为 12 行)*/