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