IF exists(SELECT * FROM sysobjects WHERE name = 'LD0SPGetSevenDaysPriorEndOfMonth')
	DROP PROCEDURE LD0SPGetSevenDaysPriorEndOfMonth
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE LD0SPGetSevenDaysPriorEndOfMonth
	@BeginDate DateTime,
    @UseOnlyWorkingDays Int,
	@ResultDate DateTime OUT
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @TargetDate DateTime
	DECLARE @LoopIndex int

	--First count backwards seven days from the end of the month
	SET @TargetDate = EOMONTH(@BeginDate);
	SET @LoopIndex = 6;

	WHILE @LoopIndex > 0
	BEGIN
		--for public holidays advance date & no counter decrement
		IF EXISTS(SELECT 1 FROM LD0V0H04 H WHERE H.DATUM = CONVERT(VARCHAR, @TargetDate, 112)
															AND DATEPART(DW, CONVERT(datetime, LEFT(H.DATUM,4) + SUBSTRING(H.DATUM,5,2) + RIGHT(H.DATUM,2),112)) NOT IN (1,7))
		BEGIN
			SET @TargetDate = DATEADD(day, -1, @TargetDate);
		END

		--for weekdays advance date & decrement counter
		if DATEPART(WEEKDAY, @TargetDate) NOT IN (1, 7)
		BEGIN
			SET @LoopIndex = @LoopIndex - 1;
			SET @TargetDate = DATEADD(day, -1, @TargetDate);
		END
		ELSE
		BEGIN
			--if weekends are included in the count
			if @UseOnlyWorkingDays != 1 --can also be interpreted as countWeekends
			BEGIN
				SET @LoopIndex = @LoopIndex - 1;
			END

			SET @TargetDate = DATEADD(day, -1, @TargetDate);
		END
	END

	SET @ResultDate = DATEADD(day, 1, @TargetDate);

	SET NOCOUNT OFF
END

GO

GRANT EXECUTE ON LD0SPGetSevenDaysPriorEndOfMonth TO LD0ROANV 