(讨论)计算当月的星期日天数问题

Andy-W 2007-02-03 09:03:54

--功能就是计算某一月份的星期日天数、应该出勤天数

--应该出勤天数=月份天数-星期日天数
--大家帮忙看看，哪里可以优化，找个最简单的方法来解决，谢谢了。
CREATE FUNCTION FUN_CoutDaysOfMonth
(@YearMonth nvarchar(7) --月份
,@CountType int --0 整月天数; 1星期日天数; 2应该出勤天数
)
RETURNS int
AS
BEGIN
DECLARE
@BeginDate datetime
,@EndDate datetime
,@Return int
,@SumSunDay int
SET @BeginDate=@YearMonth+'-01'
SET @EndDate=DATEADD(Day,-1,DATEADD(month,1,@BeginDate))
SET @Return=DATEDIFF(day,@BeginDate,@EndDate)+1
IF(@CountType=0) RETURN @Return
SET @SumSunDay=(SELECT SUM(CASE ISDATE(@YearMonth+'-'+D) WHEN 1 THEN CASE RIGHT(DATENAME(Weekday,@YearMonth+'-'+D),1) WHEN '日' THEN 1 ELSE 0 END ELSE 0 END)
FROM (
SELECT '01' D UNION ALL
SELECT '02' UNION ALL
SELECT '03' UNION ALL
SELECT '04' UNION ALL
SELECT '05' UNION ALL
SELECT '06' UNION ALL
SELECT '07' UNION ALL
SELECT '08' UNION ALL
SELECT '09' UNION ALL
SELECT '10' UNION ALL
SELECT '11' UNION ALL
SELECT '12' UNION ALL
SELECT '13' UNION ALL
SELECT '14' UNION ALL
SELECT '15' UNION ALL
SELECT '16' UNION ALL
SELECT '17' UNION ALL
SELECT '18' UNION ALL
SELECT '19' UNION ALL
SELECT '20' UNION ALL
SELECT '21' UNION ALL
SELECT '22' UNION ALL
SELECT '23' UNION ALL
SELECT '24' UNION ALL
SELECT '25' UNION ALL
SELECT '26' UNION ALL
SELECT '27' UNION ALL
SELECT '28' UNION ALL
SELECT '29' UNION ALL
SELECT '30' UNION ALL
SELECT '31'
) A
)
IF(@CountType=1) RETURN @SumSunDay
IF(@CountType=2) RETURN @Return-@SumSunDay
RETURN @Return
END

...全文