Calculations excluding weekends

Question

I am trying to make a holiday booking application and it runs of a JQuery UI calendar. the star and end date they select are passed to SQL where it puts the holiday into the requested table. the only problem is if i put a start and an end date in, it doesn't take into account the weekends. each user of the system has 21 days allowance off per calendar year and it deducts the weekends of this also. here is the current code.

SET @DateDiff = (DATEDIFF(Day,@StartDate,@EndDate) +1)

Above is the date-diff statement and below is the insertion code

BEGIN
    INSERT INTO Holidays(EmployeeID, StartDate, EndDate, Duration,Status)
    VALUES(@EmployeeID, @StartDate, @EndDate, @DateDiff,'PE')

    UPDATE Employees
    SET AnnualDaysHolidayAllowance = AnnualDaysHolidayAllowance - @DateDiff
    WHERE ID = @EmployeeID
    SELECT 'successful' AS result
END

There is more code for the business checks and rules but this is the only part i need to try and fix. i am relatively new to coding so if possible please leave an explanation to help me understand the fix, thanks.

Do you *only* need to account for weekends, or are there "standard" holidays in your area that should also be taken into account? If the latter, it's usually a pointer towards creating a calendar table that can contain information on weekends, standard holidays, and even other special events specific to your organization. — Damien_The_Unbeliever, Mar 31 '14 at 14:50
Added sql-server tag because the syntax sure looks like SQL Server. — Gordon Linoff, Mar 31 '14 at 15:02
Have a look at this article - it should help... [Stack Overflow article][1] [1]: http://stackoverflow.com/questions/252519/count-work-days-between-two-dates — Martin Milan, Mar 31 '14 at 15:02
@Damien_The_Unbeliever i only need to account for weekend the business rules leading up to this have a json file attached to retrieved standard holidays — Kieranmv95, Mar 31 '14 at 15:52

score 1 · Accepted Answer · edited May 23 '17 at 12:11

Found the solution it was on the link posted. source: Count work days between two dates The answer (not my own work)

    SET @DateDiff = (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))

Calculations excluding weekends

1 Answers1