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
4
Medium Priority
?
1,630 Views
Last Modified: 2008-01-09
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
Comment
Question by:bamaman93081
4 Comments
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 20058809

Have your dba load this into sql server and call it passing your two date variables
http://www.sqlservercentral.com/articles/Advanced+Querying/calculatingworkdays/1660/

--===== 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)
         SELECT @StartDate = DATEADD(dd,DATEDIFF(dd,0,@StartDate),0),
                @EndDate   = DATEADD(dd,DATEDIFF(dd,0,@EndDate)  ,0)

        --===== 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
              --Start with total number of days including weekends
                (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

by:
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

      date1 = date1.AddDays(1)
    End While

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

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

      date2 = date2.AddDays(-1)

    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

by:VBRocks
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

by:bamaman93081
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question