Calculating the number of working days within a date range.

• Debbie Edwards

  SSCrazy Eights

  Points: 9217

  More actions

  January 26, 2010 at 4:42 am

  #138437

  I have a table in SQL with dates (Every date within the year) and whether the day is a working day or not. E.g.

  DateWorking Day

  1990-01-01 00:00:00.000 'YES'

  1990-01-02 00:00:00.000 'YES'

  1990-01-03 00:00:00.000 'YES'

  1990-01-04 00:00:00.000 'NO'

  1990-01-05 00:00:00.000 'NO'

  1990-01-06 00:00:00.000 'NO'

  I link this to another table with a start and an end date. E.g.

  RegistrationStart DateEnd Date

  11990-01-01 00:00:00.000 1990-01-06 00:00:00.000

  I want to get the 'Number of working days between start and end'

  Looking at it I know the answer is 3. (3 yes's between these dates')

  But I cant figure out how SQL Would get me to this point. Has anyone

  got any ideas of how to do it?

  Thanks in advance

  Debbie

• dave.repton

  SSCrazy

  Points: 2468

  More actions

  January 26, 2010 at 4:55 am

  #1108565

  join the two table and add to the where calsue to specify where the working day = 'Yes'

  something like

  select Registration

  ,count(workingday)

  from a

  inner join b

  on b.date between a.startdate and a.enddate

  where b.workingday = 'Yes'

  group by registration

• Debbie Edwards

  SSCrazy Eights

  Points: 9217

  More actions

  January 26, 2010 at 5:18 am

  #1108575

  Excellent. It looks like it works. I have added an IS NULL(Date, DateDate()) so open ones count right up to todays date.

  Thanks

  Debbie