Recursive CTE's - Working Days

• justanewone

  Ten Centuries

  Points: 1242

  More actions

  June 22, 2009 at 7:42 am

  #135521

  Hi All,

  I've been playing with recursive CTE's at the moment and was just wondering if the 2 CTE's used in this code could be combined into one? As you will see, the first (recursive) CTE will get all dates from the user inputted @FromDate to the @ToDate (+ 1 Week to allow for weekends/BH's etc), adding one day recursively until it reaches the @FinishDate (+1 Week). I then have another CTE to filter out the Weekends and Bank Holidays (via a table). I can live with either having 2 CTE's or just excluding the weekends/BH's on the join (its used to see if fax details were sent by 8am next working day) but just wondering if anyone can think of a way in one CTE? Any Ideas???

  SET DATEFIRST 1

  DECLARE @FromDate DATETIME, @ToDate DATETIME

  SET @FromDate = '2009-06-01'

  SET @ToDate = '2009-06-22'

  DECLARE @StartDate DATETIME, @FinishDate DATETIME

  SET @StartDate = DATEADD(d, 0, DATEDIFF(d, 0, @FromDate))

  SET @FinishDate = DATEADD(wk, 1, DATEDIFF(d, 0, @ToDate))

  WITH WorkingDays (WorkingDay) AS

  (

  SELECT @StartDate

  UNION ALL

  SELECT DATEADD(dd, 1, WorkingDay) FROM WorkingDays WHERE WorkingDay <= @FinishDate
  )

  , WD (WorkingDay) AS
  (
  SELECT WorkingDay FROM WorkingDays
  WHERE WorkingDay NOT IN(SELECT HolidayDate FROM PublicHoliday)
  AND DATEPART(dw, WorkingDay) NOT IN(6, 7)
  )

  SELECT DATEADD(HH, 8, WorkingDay) FROM WD
  [/code]

  Thanks in advance,

  Regards,

  Dave

• Lynn Pettis

  SSC Guru

  Points: 442447

  More actions

  June 22, 2009 at 8:28 am

  #1013323

  Do a search on this site for the Tally table. You shouldn't be using a recursive CTE for this, but I could still seeing use two CTE's.