FETCH THE WEEKDAYS DATA, Sql Server

FETCH THE WEEKDAYS DATA

DECLARE @Date DATETIME
SET @Date = GETDATE() 
DECLARE @DaysToAdd INT
SET @DaysToAdd = 5

DECLARE @Count INT

DECLARE @TotalWeekEnds INT
SET @TotalWeekEnds = 0

DECLARE @Phase INT
SET @Phase = 0

CREATE TABLE #Dates 
(
	Phase INT,
	Date DateTime
)

;WITH CTE(n,Date) AS
(
	SELECT 0 AS n, @Date AS Date
	UNION ALL
	SELECT n+1 AS n, DATEADD(d,n+1, @Date) AS Date
	FROM CTE
	WHERE n < @DaysToAdd
)

INSERT INTO #Dates
SELECT 1, Date FROM CTE

SELECT @TotalWeekEnds = COUNT(*) FROM #Dates 
WHERE (DATENAME(w,Date) = 'Sunday' OR DATENAME(w,Date) = 'Saturday')

WHILE @TotalWeekEnds != 0
BEGIN
	SET @Count = 0
	SELECT @Phase = MAX(Phase) + 1 FROM #Dates
	WHILE @Count < @TotalWeekEnds
	BEGIN
		
		INSERT INTO #Dates
		SELECT @Phase, DATEADD(d,1,MAX(Date)) FROM #Dates

SET @Count = @Count + 1
	END

SELECT @TotalWeekEnds = COUNT(*) FROM #Dates 
	WHERE (DATENAME(w,Date) = 'Sunday' OR DATENAME(w,Date) = 'Saturday') AND Phase = @Phase

END

--SELECT * FROM #Dates

SELECT CONVERT(VARCHAR(10),MAX(Date),101) FROM #Dates

DROP TABLE #Dates


run \| edit \| history \| help	0