Untitled

DECLARE @P_Condition NVARCHAR(50) = 'CreateWeekOff'; --SUNDAY, SECOND AND FOURTH SATURDAY
DECLARE @StartDate DATE;
DECLARE @EndDate DATE;
DECLARE @ClassConfig NVARCHAR(MAX);
DECLARE @P_ProcMessage NVARCHAR(255);
DECLARE @P_ProcReturn INT;
DECLARE @ProcMessage NVARCHAR(255);
DECLARE @SqlQuery NVARCHAR(MAX);
DECLARE @SaturdayWeekoffFrequency NVARCHAR(50); 
DECLARE @SaturdayList TABLE (WeekNo INT);

--Exec Proc_Holiday @P_Condition='CreateWeekOff'
	If @P_Condition='CreateWeekOff'	--Running in DailyTask
	Begin
		Select Top(1) @StartDate=StartDate, @EndDate=EndDate
		from Tbl_SessionStatus
		where IsActive=1

		Select @SaturdayWeekoffFrequency = KeyValue 
		From Tbl_Settings
		Where Key1 = 'Holiday' and Key2 = 'SaturdayWeekoffFrequency' and IsActive = 1

		DECLARE @Delimiter CHAR(1) = ',';
		DECLARE @Pos INT = 1, @Len INT = LEN(@SaturdayWeekoffFrequency), @Val NVARCHAR(10);

		WHILE LEN(@SaturdayWeekoffFrequency) > 0
		BEGIN
			SET @Val = LEFT(@SaturdayWeekoffFrequency, CHARINDEX(@Delimiter, @SaturdayWeekoffFrequency + @Delimiter) - 1);
			INSERT INTO @SaturdayList (WeekNo) VALUES (CAST(@Val AS INT));
			SET @SaturdayWeekoffFrequency = STUFF(@SaturdayWeekoffFrequency, 1, CHARINDEX(@Delimiter, @SaturdayWeekoffFrequency + @Delimiter), '');
		END

		Set @ClassConfig=''
		Select @ClassConfig+=Cast(ClassSNo as varchar)+'-'+Cast(GroupSNo as varchar)+',1,0^'
		from Dbo.Fn_ClassConfig()
		order by OrderCode
 
		;With CTE(WeekOff)
		as
		(
			Select @StartDate
			union all
			Select DateAdd(d,1,WeekOff)
			from CTE
			where WeekOff<@EndDate
		)
		Insert into Tbl_Holiday(StartDate, EndDate, Mrk, Remarks, Type, ClassConfig,
		CreatedBy, CreatedOn, ModifiedBy, ModifiedOn)
		Select DateDiff(dd,0,WeekOff)+Convert(DateTime,'00:00:00.000'),
		DateDiff(dd,0,WeekOff)+Convert(DateTime,'23:59:59.997'), 1,-- DateName(dw,WeekOff),
		Case 
        When DateName(dw, WeekOff) = 'Sunday' Then 'Sunday'
		WHEN DateName(WeekDay, WeekOff) = 'Saturday' 
             and Exists (Select 1 from @SaturdayList where WeekNo = (DAY(WeekOff) - 1) / 7 + 1)
        THEN CASE (DAY(WeekOff) - 1) / 7 + 1
                WHEN 1 THEN 'First Saturday'
                WHEN 2 THEN 'Second Saturday'
                WHEN 3 THEN 'Third Saturday'
                WHEN 4 THEN 'Fourth Saturday'
                WHEN 5 THEN 'Fifth Saturday'
            END
        END,
		'Full Day', @ClassConfig,
		'Sys', GetDate(),'Sys', GetDate()
		from CTE
		where DateName(dw,WeekOff) in ('Sunday')
		--or
		--(
		--	DateName(dw, WeekOff) = 'Saturday'
		--	and 
		--	(Day(WeekOff) - 1) / 7 + 1 in (2, 4)
		--)
		OR (DateName(WeekDay, WeekOff) = 'Saturday' 
        and Exists (Select 1 from @SaturdayList where WeekNo = (DAY(WeekOff) - 1) / 7 + 1))
		and not Exists(Select 1
					from Tbl_Holiday
					where StartDate=DateDiff(dd,0,WeekOff)+Convert(DateTime,'00:00:00.000'))
		Option (MaxRecursion 366);

		Set @P_ProcMessage=Cast(@@RowCount as varchar)+' weekoff(s) created.'
		
		Exec Proc_HolidayDetail @P_Condition='SaveHolidayDetail',
		@P_ProcMessage=@ProcMessage output, @P_ProcReturn=@P_ProcReturn output

		Set @P_ProcMessage+=' '+@ProcMessage
		Set @P_ProcReturn=1
 
		Print @P_ProcMessage
	End