Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Calculate Vacation Days

Posted on 2007-10-11
Medium Priority
1,630 Views
I have a windows form with two datetimepickers.  The first is for when you leave on vacation and the second is when you return from vacation.  I would like to calculate the amount of work days (Mon thru Friday) a person is on vacation including the day they entered as first day of vacation, using the datetimepicker.value.
Example:
You take a vacation from Monday October 15 to Monday October 22, you will have been on vacation for five work days, except I want to calculate for larger spans than a week, like months at a time.
0
Question by:bamaman93081

LVL 16

Expert Comment

ID: 20058809

--===== Change current database to the Master database.
-- Allows function to be shared by everyone
USE MASTER
GO
--===== If the function already exists, drop it
IF EXISTS (
SELECT *
FROM dbo.SYSOBJECTS
WHERE ID = OBJECT_ID(N'[dbo].[fn_WorkDays]')
AND XType IN (N'FN', N'IF', N'TF')
)
DROP FUNCTION [dbo].[fn_WorkDays]
GO
CREATE FUNCTION dbo.fn_WorkDays
/***************************************************************************************
Purpose:
1.  Given any valid start date and end date, this function will calculate and return
the number of workdays (Mon - Fri).
2.  Given only a valid start date (end date has DEFAULT in it), this function will
return a 1 if the start date is a weekday and a 0 if not a weekday.

Usage:
1. MASTER.dbo.fn_WorkDays(@StartDate,@EndDate)
2. MASTER.dbo.fn_WorkDays(@StartDate,DEFAULT)    --Always returns 1 or 0
3. MASTER.dbo.fn_WorkDays(@EndDate,@StartDate)
4. MASTER.dbo.fn_WorkDays(@StartDate,@StartDate) --Always returns 1 or 0
5. MASTER.dbo.fn_WorkDays(@EndDate,@EndDate)     --Always returns 1 or 0

Notes:
1.  Holidays are NOT considered.
2.  Because of the way SQL Server calculates weeks and named days of the week, no
special consideration for the value of DATEFIRST is given.  In other words, it
doesn't matter what DATEFIRST is set to for this function.
3.  If the input dates are in the incorrect order, they will be reversed prior to any
calculations.
4.  Only whole days are considered.  Times are NOT used.
5.  The number of workdays INCLUDES both dates
6.  Inputs may be literal representations of dates, datetime datatypes, numbers that
represent the number of days since 1/1/1900 00:00:00.000, or anything else that can
be implicitly converted to or already is a datetime datatype.
7.  Undocumented: The DATEPART(dw,date) does not actually count weeks... It counts the
transition to a Sunday regardless of the DATEFIRST setting.  In essence, it counts
only whole weekends in any given date range.
8.  This UDF does NOT create a tally table or sequence table to operate.  Not only is
it set based, it is truly "tableless".

Error Indications:
1.  If either the @StartDate or the @EndDate parameter is an invalid date, the
following error is returned...
"Server: Msg 242, Level 16, State 3, Line 3
The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value."
2.  If either the @StartDate or the @EndDate parameter is a string not resembling a
date, the following error is returned...
"Server: Msg 241, Level 16, State 1, Line 3
Syntax error converting datetime from character string."
3.  If only one parameter is passed, the following error is returned...
"Server: Msg 313, Level 16, State 2, Line 3
An insufficient number of arguments were supplied for the procedure or
function MASTER.dbo.fn_WorkDays."

Revisions:
Rev 00 - 12/12/2004 - Jeff Moden - Initial creation and test.
Rev 01 - 12/12/2004 - Jeff Moden - Load test, cleanup, document, release.
Rev 02 - 12/26/2004 - Jeff Moden - Return NULL if @StartDate is NULL or DEFAULT and
modify to be insensitive to DATEFIRST settings.
***************************************************************************************/
--======================================================================================
--      Presets
--======================================================================================
--===== Define the input parameters (ok if reversed by mistake)
(
@StartDate DATETIME,
@EndDate   DATETIME = NULL --@EndDate replaced by @StartDate when DEFAULTed
)

--===== Define the output data type
RETURNS INT

AS
--======================================================================================
--      Calculate the RETURN of the function
--======================================================================================
BEGIN
--===== Declare local variables
--Temporarily holds @EndDate during date reversal
DECLARE @Swap DATETIME

--===== If the Start Date is null, return a NULL and exit
IF @StartDate IS NULL
RETURN NULL

--===== If the End Date is null, populate with Start Date value
-- so will have two dates (required by DATEDIFF below)
IF @EndDate IS NULL
SELECT @EndDate = @StartDate

--===== Strip the time element from both dates (just to be safe) by converting
-- to whole days and back to a date.  Usually faster than CONVERT.
-- 0 is a date (01/01/1900 00:00:00.000)

--===== If the inputs are in the wrong order, reverse them
IF @StartDate > @EndDate
SELECT @Swap      = @EndDate,
@EndDate   = @StartDate,
@StartDate = @Swap

--===== Calculate and return the number of workdays using the
-- input parameters.  This is the meat of the function.
-- This is really just one formula with a couple of parts
-- that are listed on separate lines for documentation
-- purposes.
RETURN (
SELECT
(DATEDIFF(dd,@StartDate,@EndDate)+1)

--Subtact 2 days for each full weekend
-(DATEDIFF(wk,@StartDate,@EndDate)*2)

--If StartDate is a Sunday, Subtract 1
-(CASE WHEN DATENAME(dw,@StartDate) = 'Sunday'
THEN 1
ELSE 0
END)

--If EndDate is a Saturday, Subtract 1
-(CASE WHEN DATENAME(dw,@EndDate) = 'Saturday'
THEN 1
ELSE 0
END)
)
END
GO
0

LVL 96

Accepted Solution

Bob Learned earned 2000 total points
ID: 20058813
Public Shared Function CalculateWorkingDays(ByVal date1 As Date, ByVal date2 As Date) As Integer

Dim days As Integer = 0

While Weekday(date1) <> vbMonday And date1 <= date2
If Weekday(date1) <> vbSaturday And Weekday(date1) <> vbSunday Then
days += 1
End If

End While

While Weekday(date2) <> vbMonday And date1 < date2

If Weekday(date2) <> vbSaturday And Weekday(date2) <> vbSunday Then
days += 1
End If

End While

If Weekday(date1) = vbMonday And Weekday(date2) = vbMonday Then
days += CInt((date2.Subtract(date1).Days / 7) * 5 + 1)
End If

Return days

End Function

Bob
0

LVL 27

Expert Comment

ID: 20059021
You know, I think you can just do it like this:

Dim firstDay As Date = #10/4/2007#
Dim lastDay As Date = #10/30/2007#

Dim vacationDays As Int16 = (lastDay.Subtract(firstDay).Days + 1) - _
(((lastDay.Subtract(firstDay).Days + 1) / 7) * 2)

msgbox vacationDays

0

Author Comment

ID: 20059206
Thanks Bob, that worked like a charm.  I just had to remove the +1 fromt he last calculation because we didn't want to include the day they returned as a vacation day.  Thanks again.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will learn different types of Android Layout and some basics of an Android App.
Make the most of your online learning experience.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
Progress
###### Suggested Courses
Course of the Month10 days, left to enroll