Table-valued functions--Custom work calendars

-- =============================================
--AUTHOR:HZF
--Create date:2016 August 16 11:25:55
--Description: Take work calendar default size weeks Statutory holidays and take some can be customized
-- =============================================
--select * from Dbo.fn_workingcalendar (' 2016-06 ', 1, ' 2016-06-14, 2016-06-06, ', ' 2016-06-01, 2016-06-02, ')
Create FUNCTION [dbo]. [Fn_workingcalendar]
(
@Month varchar (7),--1. Month
@isDax bit = 0,--2. Whether size week 0 No 1 Yes
@WKday varchar (100),--3. Specify weekday ' 2016-08-01,2016-08-16,2016-08-05 '
@Holiday varchar (100)--4. Specify Holiday ' 2016-08-01,2016-08-16,2016-08-05 '
)
RETURNS
@WK_CD TABLE (
Flag bit,--0 holiday 1 work
Wdate varchar (10),--date
Ordr int,--Item time
WEEKD varchar (14),--days of the week
Wkno INT--week
)
As
BEGIN
DECLARE @SDAT datetime
--the first day of a month
Set @SDAT = cast (@Month + ' -01 ' as datetime)

DECLARE @Cnt_Month int
--Query the number of days in a month
Set @Cnt_Month = Day (DateAdd (month,1, @SDAT)-1)

DECLARE @TDATE varchar (10)

DECLARE @Cnt int
Set @Cnt = 0
--1. Double Hugh
While @Cnt < @Cnt_Month
Begin
INSERT into @WK_CD (flags, wdate, ORDR, WEEKD, Wkno) VALUES (1, CONVERT (varchar), DATEADD (Day, @Cnt, @SDAT), @Cnt +1,d Atename (Weekday,dateadd (Day, @Cnt, @SDAT)), DatePart (Week,dateadd (day, @Cnt, @SDAT))
Update @WK_CD Set Flag = Case if Right (weekd,1) in (' One ', ' two ', ' three ', ' four ', ' five ') then 1 else 0 end
Set @Cnt = @Cnt +1
End
--Size Week
If @isDax = 1
Begin
Update @WK_CD Set Flag = 1 where right (weekd,1) = ' six ' and wkno% 2 = 1
End
DECLARE @PointerPrev INT--Start position
DECLARE @PointerCurr INT--first occurrence position
--2. Have overtime
Set @PointerPrev = 1
While @PointerPrev < LEN (@WKday)
Begin
Set @PointerCurr = CHARINDEX (', ', @WKday, @PointerPrev)
If @PointerCurr > 0
Begin
Set @TDATE = substring (@WKday, @PointerPrev, @PointerCurr-@PointerPrev)
Update @WK_CD Set Flag = 1 where wdate = @TDATE
Set @PointerPrev = @PointerCurr + 1
End
Else
Break
End
--3. Have take some
Set @PointerPrev = 1
While @PointerPrev < LEN (@Holiday)
Begin
Set @PointerCurr = CHARINDEX (', ', @Holiday, @PointerPrev)
If @PointerCurr > 0
Begin
Set @TDATE = substring (@Holiday, @PointerPrev, @PointerCurr-@PointerPrev)
Update @WK_CD Set Flag = 0 where wdate = @TDATE
Set @PointerPrev = @PointerCurr + 1
End
Else
Break
End
RETURN
END

Table-valued functions--Custom work calendars