ron.mcdowell (10/13/2011)

---

No matter what, SQL makes my head itch. So no matter what, I comment...comment..comment.

Thanks Itzik Ben-Gan for the in-line number generator, and tally table crusader Mr. Moden for getting N through my thick itchy skull.

DECLARE @StartDate date

DECLARE @DaysOut tinyint

DECLARE @DesiredDayOfWeek tinyint

DECLARE @TargetDate date

DECLARE @MaxDays int

SET @StartDate = '20111013' --The starting date

SET @DaysOut = 1 --Number of days to count out (the Desired day is AFTER this count)

SET @DesiredDayOfWeek = 6 --The desired day of week

SET @TargetDate = dateadd(dd,@DaysOut,@StartDate)

SET @MaxDays = @DaysOut + @DesiredDayOfWeek + 1 --Tally Table row count limiter

;WITH

L0 AS(SELECT 1 AS c UNION ALL SELECT 1),

L1 AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),

L2 AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),

L3 AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),

L4 AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),

L5 AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),

Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS N FROM L5)

SELECT TOP(1) @TargetDate = dateadd(dd,Tally_Table.N,@TargetDate)

FROM (SELECT TOP(@MaxDays) N FROM Nums ORDER BY N) AS Tally_Table

WHERE datepart(dw,dateadd(dd,Tally_Table.N,@TargetDate)) = @DesiredDayOfWeek

SELECT @TargetDate