Add x amount off days and don't count Weekends

• ruanp

  SSC Veteran

  Points: 292

  More actions

  April 23, 2013 at 4:39 am

  #274196

  Good Day

  I am struggling to figure this one out. I have a column with Startdate, I want to add x amount of days to that date but don't want to count weekends. if date is 2012/04/23 and I add 7 days straight it comes to 2012/04/23 without the weekends 2012/05/02 (Correct Way). I did look at WHERE WEEKDAY(date) '>' 0 AND WEEKDAY(date) '<' 5) but don't know how to implement it.

  Thanks

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  April 23, 2013 at 6:11 am

  #1609013

  -- itvf parameters

  DECLARE @Startdate DATE, @WorkingDays INT

  SELECT @Startdate = getdate(), @WorkingDays = 12

  -- row generator (see DelimitedSplit2k8 article)

  ;WITH E1(N) AS (

  SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

  SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

  SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

  ),

  E2(N) AS (SELECT 1 FROM E1 a CROSS JOIN E1 b),

  E3(N) AS (SELECT 1 FROM E2 a CROSS JOIN E2 b),

  iTally AS (

  SELECT

  NewDay = DATEADD(day,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),@Startdate)

  FROM E3),

  Calendar AS ( -- the datename filter is 26% of the cost of the query

  SELECT TOP(@WorkingDays) NewDay -- 2013-05-09

  FROM iTally

  WHERE DATENAME(weekday,NewDay) NOT IN ('Saturday','Sunday'))

  SELECT

  Startdate = @Startdate,

  WorkingDays = @WorkingDays,

  NewDay = MAX(NewDay)

  FROM Calendar

  StartdateWorkingDaysNewDay

  2013-04-23122013-05-09

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden