Calcualtion of working days.

[penfold69]

I'd like some input on the best way to calculate working days.

On the surface, the problem seems simple - work out how many 'working days' are between two dates.

For the purposes of this, a 'working day' is considered Monday to Friday. The tricky part is excluding Bank holidays, and Employee Holidays!

I've currently plumped for a method that does the following:

	Public Function CalculateWorkingDaysBetweenDates(ByRef starttime As Date, ByRef endtime As Date, ByRef Rep As Long) As Long
		' This function will calculate the number of working days between two dates

		Dim datecount As DateTime = starttime
		Dim endDays As Integer = 0
		Dim sSql As String

		' Check for rep holidays that start before and end after this period
		sSql = "SELECT COUNT(*) FROM LU_AM_HOLIDAY WHERE REP=" & Rep
		sSql &= " AND (START_DATE<'" & DateToSql(starttime.Date) & "' AND END_DATE>'" & DateToSql(endtime.Date) & "') "
		If App.DB.QueryDatabase(sSql) > 0 Then
			' The holiday completely covers the requested time span
                        endDays = 0
			Return endDays
		End If

		While (datecount.Date <= endtime.Date)
			Select Case datecount.DayOfWeek
				Case DayOfWeek.Saturday
				Case DayOfWeek.Sunday
				Case Else
					endDays += 1
			End Select
			datecount = datecount.AddDays(1)
		End While

		sSql = "SELECT COUNT(*) FROM LU_BHOL WHERE BHOL>='" & DateToSql(starttime.Date) & "' AND BHOL<='" & DateToSql(endtime.Date) & "' "
		Dim bhols As Long = Val(App.DB.QueryDatabase(sSql))

		endDays -= bhols

		' check for rep holidays that fall completely between these dates.
		sSql = "SELECT SUM(DAYS) FROM LU_AM_HOLIDAY WHERE REP=" & Rep
		sSql &= " AND (START_DATE>='" & DateToSql(starttime.Date) & "' AND END_DATE<='" & DateToSql(endtime.Date) & "') "

		Dim rephols As Long = Val(App.DB.QueryDatabase(sSql))
		endDays -= rephols

		' Check for rep holidays that start before this period and end during
		sSql = "SELECT * FROM LU_AM_HOLIDAY WHERE REP=" & Rep
		sSql &= " AND (START_DATE<'" & DateToSql(starttime.Date) & "' AND END_DATE>='" & DateToSql(starttime.Date) & "' AND END_DATE<= '" & DateToSql(endtime.Date) & "') "
		Dim dt As DataTable = App.DB.RetrieveTable(sSql)
		For index As Integer = 0 To dt.Rows.Count - 1
			Dim row As DataRow = dt.Rows(index)
			rephols = CDate(row("END_DATE")).Subtract(starttime).Days
			endDays -= rephols
		Next

		' Check for rep holidays that start during this period and end after
		sSql = "SELECT * FROM LU_AM_HOLIDAY WHERE REP=" & Rep
		sSql &= " AND (START_DATE>'" & DateToSql(starttime.Date) & "' AND START_DATE <= '" & DateToSql(endtime.Date) & "' AND END_DATE>'" & DateToSql(endtime.Date) & "') "
		dt = App.DB.RetrieveTable(sSql)
		For index As Integer = 0 To dt.Rows.Count - 1
			Dim row As DataRow = dt.Rows(index)
			rephols = endtime.Subtract(row("START_DATE")).Days
			endDays -= rephols
		Next

		dt.Dispose()

		Return endDays
End Function

As you can see, this function makes several round trips to the server to request various records containing holidays and bank holidays. However, I'm looking for a neater solution.

In addition, I would like a function that will take a start date, and a number of working days, and give me an end date. This too seems like a relatively simple problem. However, it actually is a little more complicated - as you discover 'skippable' days in your timespan, you have to extend your timespan, and therefore discover more 'skippable' days!

In all, its a tricky problem that I'd like to elicit some discussion on - perhaps someone has tackled this before?

Barry.

 

[mocella]

You could probably combine the last two queries via the UNION operator for sure.

One thing - what kind of database are you using? If it's sql-server, you could probably just write a stored proc to process all the logic in one call to the server.

I'm still tired so can't think about the rest of your code yet. Sorry