SQL server DATEADD specific week days

Question

So I need to add some days to a date, let's say 30, but the 30 days can't be calendar days, they are parameters that depend on some logic behind, so, I need to find a way to add to a date 30 days between mondays and wednesday for example: If I add 30 (mon to wed) days to february 15th, I should get April 26 If I add 30 (sun to fri) days to february 15th, I should get March 17

If the situation is not clear enough just let me know ant I'll try to give a better explanation.

Thanks.

Answer 1

First I've generated a series of dates between StartDate and EndDate, but it returns Day of Week:

SELECT  DATEPART(weekday, DATEADD(DAY, nbr - 1, @StartDate)) as dow
FROM    (SELECT    ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS Nbr
         FROM      sys.columns c
         )nbrs
WHERE   nbr - 1 <= DATEDIFF(DAY, @StartDate, @EndDate)

Then you can use a simple WHERE IN () to select wich day of week you want to include, and count the returned days.

DECLARE @StartDate DATE = '20170101'
        , @EndDate DATE = '20170131'

SELECT COUNT(*) AS DAYS
FROM (
        SELECT  DATEPART(weekday, DATEADD(DAY, nbr - 1, @StartDate)) as dow
        FROM    (SELECT    ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS Nbr
                 FROM      sys.columns c
                )nbrs
        WHERE   nbr - 1 <= DATEDIFF(DAY, @StartDate, @EndDate)
    ) T1
WHERE DOW IN (3,4,5);

Take care of firs day of week in your SQL Server, you can change it with SET DATEFIRST.

Check it here: http://rextester.com/WCLIXM28868