Database

ALTER PROCEDURE [dbo].[GetWeekNumberList]
@IncludeCurrentWeek BIT = NULL

AS
BEGIN
SET DATEFIRST 1;
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
    -- Insert statements for procedure here
	CREATE TABLE #tempWeekList(weekDate DATETIME, weekLabel VARCHAR(25))

	DECLARE @Counter INT,
		@StartDate DATETIME,
		@EndDate DATETIME


	IF @IncludeCurrentWeek IS NOT NULL AND @IncludeCurrentWeek = 1
		BEGIN
			SET @StartDate = DATEADD(dd, (DATEPART(dw, GETDATE()) * -1) + 1, GETDATE())
		END
	ELSE
		BEGIN
			SET @StartDate = DATEADD(dd, (DATEPART(dw, DATEADD(dd, -7, GETDATE())) * -1) + 1, DATEADD(dd, -7, GETDATE()))
		END
	SET @EndDate = DATEADD(dd, 4, @StartDate)
	SET @Counter = 1
	

	WHILE @Counter <= 12
	BEGIN
		INSERT INTO #tempWeekList
		VALUES (@EndDate, CAST(DATEPART(wk, @EndDate) AS VARCHAR) + ': ' + CONVERT(VARCHAR, @StartDate, 6))
		SET @StartDate = DATEADD(dd, -7, @StartDate)
		SET @EndDate = DATEADD(dd, 4, @StartDate)
		SET @Counter = @Counter + 1
	END

	SELECT * FROM #tempWeekList

	DROP TABLE #tempWeekList
END