(讨论)计算当月的星期日天数问题
--功能就是计算某一月份的星期日天数、应该出勤天数
--应该出勤天数=月份天数-星期日天数
--大家帮忙看看，哪里可以优化，找个最简单的方法来解决，谢谢了。
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
[解决办法]
像LZ這樣運算的...要是碰到個國慶節,勞動節,春節之類的,還不要更麻煩
[解决办法]
乳沟兄好有经验哦...
[解决办法]
慚愧慚愧...偶是CSDN學到的^^
