Count work days between two dates

Question

Really need some JQuery help here. I'm about to launch my laptop out the window. I have come a long way with this piece of code an I think I am almost there but I am stuck on the last hurdle.

I am only going to include the pertinent pieces of code here because it is a very large piece.

I have a navigation menu for a mock solar system. Here is the link to the larger external piece if you want to see the whole thing. http://jsbin.com/zagiko/1/edit (please note this uses mostly CSS3).

I have a nav menu for the piece and when you click on the values in the nav menu the current script assigns a class of active. That all works perfectly. I built in a button to test the active state on click and the state changes are working. But I need it to respond to the state change on hover. I am not a JQuery person; I am learning. It almost seems like the hover isn't working because it is responding to the data loaded when the page loads instead of responding in real time. But I am just guessing.

What I need is an if statement that will respond to the live data (not on page load or when the document is ready). The if statement should basically say if this div is active then this other div can appear on hover. But if this div is not active then it cannot appear.

The current if statement I wrote is

if($("a.active").is('.uranus')){
    $('#uranus .infos').hover( 
        function () {
            $("#descriptionsp").fadeIn("2000");
        })
    };

The current script that runs when the site loads that sets up the menus is:

$(window).load(function(){
    var e=$("body"),
        t=$("#universe"),
        n=$("#solar-system"),
        r=function() {
            e.removeClass("view-2D opening").addClass("view-3D").delay(2e3).queue(function() {
                $(this).removeClass("hide-UI").addClass("set-speed");

                $(this).dequeue()})
        },
        i=function(e){
            t.removeClass().addClass(e)
        };

    $("#toggle-data").click(function(t){
        e.toggleClass("data-open data-close");
        t.preventDefault()
    });

    $("#toggle-controls").click(function(t){
        e.toggleClass("controls-open controls-close");
        t.preventDefault()
    });

    $("#data a").click(function(e){
        var t=$(this).attr("class");
        n.removeClass().addClass(t);
        $(this).parent().find("a").removeClass("active");
        $(this).addClass("active");
        e.preventDefault()
    });

Really need you help. Thanks in advance!

Solution 1

For workdays, Monday to Friday, you can do it with a single SELECT, like this:

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2008/10/01'
SET @EndDate = '2008/10/31'


SELECT
   (DATEDIFF(dd, @StartDate, @EndDate) + 1)
  -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
  -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)

If you want to include holidays, you have to work it out a bit...

Solution 2

In Calculating Work Days you can find a good article about this subject, but as you can see it is not that advanced.

--Changing 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
--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

If you need to use a custom calendar, you might need to add some checks and some parameters. Hopefully it will provide a good starting point.

Solution 3

All Credit to Bogdan Maxim & Peter Mortensen. This is their post, I just added holidays to the function (This assumes you have a table "tblHolidays" with a datetime field "HolDate".

--Changing 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
--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)
        --Subtract all holidays
        -(Select Count(*) from [DB04\DB04].[Gateway].[dbo].[tblHolidays]
          where  [HolDate] between @StartDate and @EndDate )
        )
    END  
GO
-- Test Script
/*
declare @EndDate datetime= dateadd(m,2,getdate())
print @EndDate
select  [Master].[dbo].[fn_WorkDays] (getdate(), @EndDate)
*/

Solution 4

My version of the accepted answer as a function using DATEPART, so I don't have to do a string comparison on the line with

DATENAME(dw, @StartDate) = 'Sunday'

Anyway, here's my business datediff function

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION BDATEDIFF
(
    @startdate as DATETIME,
    @enddate as DATETIME
)
RETURNS INT
AS
BEGIN
    DECLARE @res int

SET @res = (DATEDIFF(dd, @startdate, @enddate) + 1)
    -(DATEDIFF(wk, @startdate, @enddate) * 2)
    -(CASE WHEN DATEPART(dw, @startdate) = 1 THEN 1 ELSE 0 END)
    -(CASE WHEN DATEPART(dw, @enddate) = 7 THEN 1 ELSE 0 END)

    RETURN @res
END
GO

Solution 5

Another approach to calculating working days is to use a WHILE loop which basically iterates through a date range and increment it by 1 whenever days are found to be within Monday – Friday. The complete script for calculating working days using the WHILE loop is shown below:

CREATE FUNCTION [dbo].[fn_GetTotalWorkingDaysUsingLoop]
(@DateFrom DATE,
@DateTo   DATE
)
RETURNS INT
AS
     BEGIN
         DECLARE @TotWorkingDays INT= 0;
         WHILE @DateFrom <= @DateTo
             BEGIN
                 IF DATENAME(WEEKDAY, @DateFrom) IN('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday')
                     BEGIN
                         SET @TotWorkingDays = @TotWorkingDays + 1;
                 END;
                 SET @DateFrom = DATEADD(DAY, 1, @DateFrom);
             END;
         RETURN @TotWorkingDays;
     END;
GO

Although the WHILE loop option is cleaner and uses less lines of code, it has the potential of being a performance bottleneck in your environment particularly when your date range spans across several years.

You can see more methods on how to calculate work days and hours in this article: https://www.sqlshack.com/how-to-calculate-work-days-and-hours-in-sql-server/

View more solutions

Comments

[Dave K]

Can you define workdays? any Monday through friday? Excluding major holidays? What country? Must it be done in SQL?

[Huangism]

I feel like what you are trying to do can be done in css. When the item is active, you should be able to define the hover fading effect with css3.

[Thesis]

Hi @Huangism. I am trying to do it with either JavaScript or JQuery because I already have to design an entirely separate 2D piece for IE after I finish this because IE can't handle the transitions. When I get to the 2D version for IE I still need atleast some of the nicer feature to be able to work in IE because I will already been losing the 3D effect of the planets. Plus I know that I am right there. I just need the right JQUERY person to tweak this.

[John Sterling]

Your question is very verbose, and I'm having trouble figuring out exactly what you want to do. When I click, for example, "spirituality" in the menu, you want the related planet to respond to hover event, am I correct?

[Huangism]

@ThesisDesign I think the answer is probably quite simple but because there is a lot to read I can't quite understand exactly what you are trying to accomplish. If you can simplify this down, it can probably be solved quite easily

[Thesis]

Hi @JohnSterling. Sorry for the lengthy explanation. It's a large piece and I didn't want to leave anything out. Yes, you are in the ballpark. The current if statement that I wrote is attached to "spirituality". When you click on "spirituality" it opens the plant ID div and it Info for that div and I want the hover event to trigger only if the info div is active/visible. The others are just written as standard hovers without the "If". The problem with the design without the "If" is that if you allow you mouse to glide over the design it will trigger the hidden divs.

[Thesis]

One last note to clarify @JohnSterling. The hover event should trigger if the info div for the planet is visible and you hover over it. Then the description div should appear.

[marc_s]

If you post code, XML or data samples, please highlight those lines in the text editor and click on the "code samples" button ( { } ) on the editor toolbar to nicely format and syntax highlight it!

[greektreat]

I just realized that this code doesn't work always! i tried this: SET @StartDate = '28-mar-2011' SET @EndDate = '29-mar-2011' the answer it counted it as 2 days

[user3156301]

@greektreat It works fine. It's just that both @StartDate and @EndDate are included in the count. If you want Monday to Tuesday to count as 1 day, just remove the "+ 1" after the first DATEDIFF. Then you'll also get Fri->Sat=0, Fri->Sun=0, Fri->Mon=1.

[Brian Scott]

Great, no need for periphery functions or updates to the database using this. Thanks. Love the saltire btw :-)

[Sequenzia]

As a followup to @JoeDaley. When you remove the + 1 after the DATEDIFF to exclude the startdate from the count you also need to adjust the CASE part of this. I ended up using this: +(CASE WHEN DATENAME(dw, @StartDate) = 'Saturday' THEN 1 ELSE 0 END) - (CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)

[Torben Klein]

The datename function is locale-dependent. A more robust but also more obscure solution is to replace the last two lines by: -(case datepart(dw, @StartDate)+@@datefirst when 8 then 1 else 0 end) -(case datepart(dw, @EndDate)+@@datefirst when 7 then 1 when 14 then 1 else 0 end)

[Nate Cook]

On my machine the replacement line suggested by @TorbenKlein does not yield the same results as using the DATENAME function. (@@datefirst on my machine is 7.) Unfortunately I don't have time right now to troubleshoot it, but I wanted to leave a comment about it.

[Dave]

Adding the count of Holidays to this makes it complete.

[RAmPE]

Thanks for including the link to understand how this works. The write on sqlservercentral was great!

[Illia Ratkevych]

Concerning your holidays subtractions. What if start date is January 1 and end date is December 31? You will subtract only 2 - which is wrong. I propose to use DATEDIFF(day, Start_Date, Date) and same for End_Date instead of whole 'SELECT COUNT(*) FROM Holiday ...'.

[Julio Nobre]

Hi Dan B. Just to let you know that your version assumes that table tblHolidays do not contain Saturdays and Mondays, which, sometimes happens. Anyway, thanks for sharing your version. Cheers

[Danimal111]

Julio - Yes - My version does assume that Saturday's and Sundays (not Monday's) are weekends, and therefor not "non-business" day. But if you're working weekends, then I guess everyday is a "workday" and you can comment out the Saturday & Sunday part of the clause and just add in all your holidays to the tblHolidays table.

[Si8]

What if I have this question and I want to add your query. How can I combine them? stackoverflow.com/questions/23349151/…

[Huangism]

Also it makes more reading sense if you do if($(".uranus").is('.active')) that's just my opinion, as that read if Uranus is active

[Thesis]

@BrianGlaz YOU NAILED IT! Thank you so much! I think I was 2 hours away from smoking again. It works perfectly. Thanks to everyone else for your suggestions as well. Again, Brian thank :).

[James Jenkins]

+1 I ended up using a similar solution here

[James Jenkins]

If you are going to use a function, it might be better to go with a table based function as in the answer by Mário Meyrelles

[Andy Raddatz]

To clarify @Sequenzia's comment, you would REMOVE the case statements about Sunday entirely, leaving only +(CASE WHEN DATENAME(dw, @StartDate) = 'Saturday' THEN 1 ELSE 0 END) - (CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)

[Roni Castro]

This website explains how this formula works: sqlservercentral.com/articles/Advanced+Querying/…

[Sirke]

Those holiday dates might fall on weekends too. And for some, holiday on Sunday will be replaced by the next Monday.

[Andrew]

Would be nice to also explain the SQL, pure code answers are not good, people are asking questions because they don't know how, just giving the answers helps nobody learn.

[adrianm]

Note that this will return wrong results for many dates in weekends so they don't add upp (Fri->Mon should be same as Fri->Sat + Sat->Sun + Sun->Mon). Fri->Sat should be 0 (correct), Sat->Sun should be 0 (wrong -1), Sun->Mon should be 1 (wrong 0). Other errors following from this is Sat->Sat = -1, Sun->Sun = -1, Sun->Sat = 4

[shawnt00]

@adrianm I believe I had corrected the issues. Actually the problem was that it was always off by one because I had somehow dropped that part by accident.

[adrianm]

Thanks for the update. I thought your formula was excluding start date which is what I needed. Solved it myself and added it as another answer.

[AlsoKnownAsJazz]

Thanks Dan. I incorporated this into my function, adding a check for weekends as my DateDimensions table includes all dates, holidays, etc. Taking your function, I just added: and IsWeekend = 0 after where [HolDate] between StartDate and EndDate )

[Andre]

If the Holiday table contains holidays on weekends, you can amend the criteria like this: WHERE HolDate BETWEEN @StartDate AND @EndDate AND DATEPART(dw, HolDate) BETWEEN 2 AND 6 to only count holidays from Monday to Friday.

[Hilary]

Super solution. I subbed in formulae for variables to use in a webi Universe to calculate weekdays (M-F) between the dates in 2 table columns like so ...((((DATEDIFF(day, table.col1, table.col2) +1)-((CASE DATENAME(weekday, table.col2) WHEN 'Saturday' THEN 1 WHEN 'Sunday' THEN 2 ELSE 0 END )))/7)*5)+(((DATEDIFF(day, table.col1, table.col2) +1)-((CASE DATENAME(weekday, table.col2) WHEN 'Saturday' THEN 1 WHEN 'Sunday' THEN 2 ELSE 0 END )))%7)

[caiosm1005]

That one did it for me but I had to do a small change. It wasn't accounting for when @StartDate is a Saturday or Friday. Here's my version: DATEDIFF(day, @StartDate, @EndDate) - DATEDIFF(week, @StartDate, @EndDate) - DATEDIFF(week, DATEADD(day, 1, @StartDate), DATEADD(day, 1, @EndDate)) - (CASE WHEN DATEPART(WEEKDAY, @StartDate) IN (1, 7) THEN 1 ELSE 0 END) + 1

[adrianm]

@caiosm1005, saturday to sunday returns 0, saturday to monday returns 1, friday to saturday returns 0. All are consistent with my definition. Your code will not accumulate correctly (e.g. return 6 for friday to friday but 5 for monday to monday)

[Phantom]

Perfect! This is what I was looking for. Special thanks!

[Igor Krupitsky]

I just added this code a an article in code project codeproject.com/Tips/5284659/…

[Caius Jard]

This is logically identical to your other answer to this same question, but with more line breaks