Less hideous way to do this?

Transact-SQL
1

I wrote a query that works, but I am convinced there was a better way to do it.

The goal was that for any given date provided, it would create a two-week range (start date & end date) which began on a Tuesday of an odd week & ended on the following Tuesday, two weeks later, which would begin the next odd week.

It works & there aren't efficiency concerns so I've put it into production. But boy is this awful-looking code that I wouldn't want other people to see that I wrote... Is there a better way?

DECLARE @date DATETIME = GETDATE() - 14
;

WITH week_data AS (
	SELECT 
		DATEPART(DW, @date)			AS [DayOfWeek]
		, CAST( @date AS DATE)		AS [Date]
		, DATEPART(WEEK, @date)		AS [WeekNum]
	UNION ALL 
	SELECT 
		DATEPART(DW, @date-1)
		, CAST( @date-1 AS DATE)
		, DATEPART(WEEK, @date-1)
	UNION ALL 
	SELECT 
		DATEPART(DW, @date-2)
		, CAST( @date-2 AS DATE)
		, DATEPART(WEEK, @date-2)
	UNION ALL 
	SELECT 
		DATEPART(DW, @date-3)
		, CAST( @date-3 AS DATE)
		, DATEPART(WEEK, @date-3)
	UNION ALL 
	SELECT 
		DATEPART(DW, @date-4)
		, CAST( @date-4 AS DATE)
		, DATEPART(WEEK, @date-4)
	UNION ALL 
	SELECT 
		DATEPART(DW, @date-5)
		, CAST( @date-5 AS DATE)
		, DATEPART(WEEK, @date-5)
	UNION ALL 
	SELECT 
		DATEPART(DW, @date-6)
		, CAST( @date-6 AS DATE)
		, DATEPART(WEEK, @date-6)

	UNION ALL
	SELECT 
		DATEPART(DW, @date-7)
		, CAST( @date-7 AS DATE)
		, DATEPART(WEEK, @date-7)
	UNION ALL 
	SELECT 
		DATEPART(DW, @date-8)
		, CAST( @date-8 AS DATE)
		, DATEPART(WEEK, @date-8)
	UNION ALL 
	SELECT 
		DATEPART(DW, @date-9)
		, CAST( @date-9 AS DATE)
		, DATEPART(WEEK, @date-9)
	UNION ALL 
	SELECT 
		DATEPART(DW, @date-10)
		, CAST( @date-10 AS DATE)
		, DATEPART(WEEK, @date-10)
	UNION ALL 
	SELECT 
		DATEPART(DW, @date-11)
		, CAST( @date-11 AS DATE)
		, DATEPART(WEEK, @date-11)
	UNION ALL 
	SELECT 
		DATEPART(DW, @date-12)
		, CAST( @date-12 AS DATE)
		, DATEPART(WEEK, @date-12)
	UNION ALL 
	SELECT 
		DATEPART(DW, @date-13)
		, CAST( @date-13 AS DATE)
		, DATEPART(WEEK, @date-13)
	)
SELECT 
	CAST( [Date] AS NVARCHAR(MAX))							AS [StartDate]
	, CAST( DATEADD(DAY, 14, [Date]) AS NVARCHAR(MAX))		AS [EndDate]
FROM week_data
WHERE DayOfWeek =3
	AND ( [WeekNum] % 2 ) != 0
2 
DECLARE @date DATETIME = GETDATE() - 14;

SELECT StartDate, DATEADD(DAY, 14, StartDate) AS EndDate
FROM (
SELECT CASE WHEN DATEPART(WEEK, date) % 2 = 0 THEN DATEADD(DAY, -7, date)
    ELSE date END AS StartDate
FROM (
    SELECT date = CAST(DATEADD(DAY, -DATEDIFF(DAY, 1, @date) % 7, @date) AS date)
) AS find_most_recent_Tuesday
) AS adjust_start_date
1 Like