I've read nearly every question and I must be overlooking something simple, but I cannot get this to calculate correctly for all scenarios. I have a date, and a reference table of different integers that I need to add to specific dates on each row. So for the sake of this question, I've just defined them specifically instead of a more complicated query.
For Date A, I need to add 7 days to the date, but exclude weekends.
For Date B, I need to add 5 days, but same as above, exclude weekends.
I've referenced the code from here,
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2008/10/01'
SET @EndDate = '2008/10/31'
SELECT
(DATEDIFF(dd, @StartDate, @EndDate) + 1)
-(DATEDIFF(wk, @StartDate, @EndDate) * 2)
-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)
And applied it using my own logic to get this
SELECT '11/05/2015' AS [A Begin]
, 7 AS [A Add]
, CAST(DATEADD(day, ((DATEDIFF(wk, '11/05/2015', DATEADD(day, 7, '11/05/2015')) * 2)
+ (CASE WHEN DATENAME(dw, '11/05/2015') = 'Sunday' THEN 1 ELSE 0 END)
+ (CASE WHEN DATENAME(dw, DATEADD(day, 7, '11/05/2015')) = 'Saturday' THEN 2 ELSE 0 END) + 7), '11/05/2015') AS DATETIME)
+ CAST('16:00' AS DATETIME) AS [A End]
, '11/05/2015' AS [B Begin]
, 5 AS [B Add]
, CAST(DATEADD(day, ((DATEDIFF(wk, '11/05/2015', DATEADD(day, 5, '11/05/2015')) * 2)
+ (CASE WHEN DATENAME(dw, '11/05/2015') = 'Sunday' THEN 1 ELSE 0 END)
+ (CASE WHEN DATENAME(dw, DATEADD(day, 5, '11/05/2015')) = 'Saturday' THEN 2 ELSE 0 END) + 5), '11/05/2015') AS DATETIME)
+ CAST('16:00' AS DATETIME) AS [B End]
For Range A, its adding 7 days, but lands on a Saturday as the End.. which I cannot have, it needs to equal 11/16/2015 to be correct. Range B works just fine. I am not able to perform Declares or any other functions.
