How can I calculate the number of work days between two dates from table (from the 1st row to the end) in SQL Server 2008?
I tried something like this, but it does not work
DECLARE @StartDate as DATETIME, @EndDate as DATETIME
Select @StartDate = date2 from testtable ;
select @EndDate = date1 from testtable ;
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)
I would always recommend a Calendar table, then you can simply use:
SELECT COUNT(*)
FROM dbo.CalendarTable
WHERE IsWorkingDay = 1
AND [Date] > @StartDate
AND [Date] <= @EndDate;
Since SQL has no knowledge of national holidays for example the number of weekdays between two dates does not always represent the number of working days. This is why a calendar table is a must for most databases. They do not take a lot of memory and simplify a lot of queries.
But if this is not an option then you can generate a table of dates relatively easily on the fly and use this
SET DATEFIRST 1;
DECLARE @StartDate DATETIME = '20131103',
@EndDate DATETIME = '20131104';
-- GENERATE A LIST OF ALL DATES BETWEEN THE START DATE AND THE END DATE
WITH AllDates AS
( SELECT TOP (DATEDIFF(DAY, @StartDate, @EndDate))
D = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.Object_ID), @StartDate)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
)
SELECT WeekDays = COUNT(*)
FROM AllDates
WHERE DATEPART(WEEKDAY, D) NOT IN (6, 7);
EDIT
If you need to calculate the difference between two date columns you can still use your calendar table as so:
SELECT t.ID,
t.Date1,
t.Date2,
WorkingDays = COUNT(c.DateKey)
FROM TestTable t
LEFT JOIN dbo.Calendar c
ON c.DateKey >= t.Date1
AND c.DateKey < t.Date2
AND c.IsWorkingDay = 1
GROUP BY t.ID, t.Date1, t.Date2;
Example on SQL-Fiddle
This does it excluding the days out but date part rather than description. You can substitute the parameters used as an example for the values in your query.
Declare
@startdate datetime = '2013-11-01',
@enddate datetime = '2013-11-11'
SELECT
(DATEDIFF(dd, @StartDate, @EndDate) + 1)
-(DATEDIFF(wk, @StartDate, @EndDate) * 2)
-(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)
Returns 7
To add to GarethD's answer - I put together the SQL for a USA version of the Calendar table, with all holidays and weekends set to is_working_day = false... for anyone that would like the SQL, here it is:
declare @start_dt as date = '1/1/2009'; -- Date from which the calendar table will be created.
declare @end_dt as date = '1/1/2030'; -- Calendar table will be created up to this date (not including).
create table CalendarTable (
date_id date primary key,
date_year smallint,
date_month tinyint,
date_day tinyint,
weekday_id tinyint,
weekday_nm varchar(10),
month_nm varchar(10),
day_of_year smallint,
quarter_id tinyint,
first_day_of_month date,
last_day_of_month date,
start_dts datetime,
end_dts datetime,
week_number_of_month int,
is_working_day bit,
)
while @start_dt < @end_dt
begin
insert into CalendarTable(
date_id, date_year, date_month, date_day,
weekday_id, weekday_nm, month_nm, day_of_year, quarter_id,
first_day_of_month, last_day_of_month,
start_dts, end_dts, week_number_of_month, is_working_day
)
values(
@start_dt, year(@start_dt), month(@start_dt), day(@start_dt),
datepart(weekday, @start_dt), datename(weekday, @start_dt), datename(month, @start_dt), datepart(dayofyear, @start_dt), datepart(quarter, @start_dt),
dateadd(day,-(day(@start_dt)-1),@start_dt), dateadd(day,-(day(dateadd(month,1,@start_dt))),dateadd(month,1,@start_dt)),
cast(@start_dt as datetime), dateadd(second,-1,cast(dateadd(day, 1, @start_dt) as datetime)), DATEDIFF(week, DATEADD(MONTH, DATEDIFF(MONTH, 0, @start_dt), 0), @start_dt) +1, 0
)
set @start_dt = dateadd(day, 1, @start_dt)
end
GO
-- Set all non-weekend days as business days
update CalendarTable set is_working_day = 1 where weekday_id not in (1,7)
GO
-- New Years Day
update CalendarTable set is_working_day = 0 where date_month = 1 and date_day = 1
GO
-- Memorial Day (last Monday of May)
WITH Memorial_Day AS
(
SELECT date_id, date_year, date_day,
ROW_NUMBER() OVER (PARTITION BY date_year ORDER BY date_day desc) AS RowNumber
FROM CalendarTable
where date_month = 5 and weekday_id = 2
)
update CalendarTable set is_working_day = 0 where date_id in (SELECT date_id FROM Memorial_Day
where rownumber = 1)
GO
-- Independence Day
update CalendarTable set is_working_day = 0 where date_month = 7 and date_day = 4
GO
-- Labor Day (first Monday in September)
WITH Labor_Day AS
(
SELECT date_id, date_year, date_day,
ROW_NUMBER() OVER (PARTITION BY date_year ORDER BY date_day) AS RowNumber
FROM CalendarTable
where date_month = 9 and weekday_id = 2
)
update CalendarTable set is_working_day = 0 where date_id in (SELECT date_id FROM Labor_Day
where rownumber = 1)
GO
-- Thanksgiving (fourth Thursday in November)
WITH Thanksgiving AS
(
SELECT date_id, date_year, date_day,
ROW_NUMBER() OVER (PARTITION BY date_year ORDER BY date_day) AS RowNumber
FROM CalendarTable
where date_month = 11 and weekday_id = 5
)
update CalendarTable set is_working_day = 0 where date_id in (SELECT date_id FROM Thanksgiving
where rownumber = 4)
GO
-- Day After Thanksgiving (fourth Friday in November)
WITH DayAfterThanksgiving AS
(
SELECT date_id, date_year, date_day,
ROW_NUMBER() OVER (PARTITION BY date_year ORDER BY date_day) AS RowNumber
FROM CalendarTable
where date_month = 11 and weekday_id = 6
)
update CalendarTable set is_working_day = 0 where date_id in (SELECT date_id FROM DayAfterThanksgiving
where rownumber = 4)
GO
-- Christmas Day
update CalendarTable set is_working_day = 0 where date_month = 12 and date_day = 25
GO
You Can simply use datediff function of sql. and then you can subtract weekends between those dates if any. For example check below query.
You also can calculate holidays between start/end date and can subtract that from final selection.
Declare @startdate as DateTime
Declare @enddate as DateTime
Set @startdate = GETDATE()-2;
set @enddate = GETDATE()+3;
select @startdate,@enddate,(datediff(day,@startdate,@enddate+1)-(2)*datediff(week,@startdate,@enddate))
Interesting question. It is always important to understand the use case. If counting from Sunday to Monday, would we want to say there is one day as if we had until close of business on Monday. Or would we want to say there are no days as if there were no days before Monday began. In our case we needed to count both days (start and end) if they were weekdays, because I was working in a payroll application estimating some accruals. And any day weekend days would later be accounted for by holiday, and overtime records.
When I pulled out my calendar I realized that when counting from Saturday or Sunday we could just start counting from Monday. And also when counting to a Saturday or Sunday I could just stop counting when we got to Friday. So I wrote a function that adjusted the starting and ending dates, Found the number of weeks by dividing by 7, multiplied by that 5 weekdays per week and then added back the remainder. I did have to account for the case when we started counting on a weekend but never got to a Monday.
-- =============================================
-- Author: Todd P Payne
-- Create date: 9/1/2018
-- Description: Counts number of weekdays between two dates
-- Unlike DateDiff StartDate and EndDate are inclusive
-- FROM Monday, Jan 1 to Monday Jan 1 will return 1
-- =============================================
CREATE FUNCTION [dbo].[ufnCountWeekdays]
(
-- Add the parameters for the function here
@StartDate DateTime,
@EndDate DateTime
)
RETURNS INT
AS
BEGIN
-- Declare the return variable here
DECLARE @CountofWeekDays INT = NULL;
DECLARE @TempDate DateTime;
-- Could CountBackwords
IF @StartDate > @ENDDate
BEGIN
SET @TempDate = @StartDate;
SET @StartDate = @EndDate;
SET @EndDate = @TempDate;
END
--Start on Weekend Never get to Monday Case
IF (DatePart(dw,@StartDate) = 7 AND DateDiff(Day,@StartDate,@EndDate) < 2)
OR (DatePart(dw,@StartDate) = 1 AND DateDiff(Day,@StartDate,@EndDate) < 1 )
BEGIN
SET @CountOfWeekDays = 0 -- Never got to a WeekDay
END
--NORMAL CASE
ELSE BEGIN
-- IF Sat Start just pretend Start Counting on Monday
IF (DatePart(dw,@StartDate) = 7) SET @StartDate = dateadd(Day, 2, @StartDate);
-- IF Sun Start just Start to Counting on Monday
IF (DatePart(dw,@StartDate) = 1) SET @StartDate = dateadd(Day, 1, @StartDate);
-- Sat End just Stop counting on Friday
IF (DatePart(dw,@EndDate) = 7) SET @EndDate = DateAdd(Day, -1, @EndDate);
-- Sun END
if (DatePart(dw,@EndDate) = 1) SET @EndDate = DateAdd(Day, -2, @EndDate);
--Find the total number of days we need to count
Declare @DaysToCount INT = DateDiff(Day,@StartDate, @EndDate)+1; --include start
-- five days for each full week plus any other weekdays
-- remember no worries about starting or ending on weekends
Set @CountofWeekDays = Floor(@DaysToCount/7)*5 + (@DaysToCount % 7)
END
--Check to see if we are counting backwards
IF @TempDate = @EndDate SET @CountofWeekDays = -1 * @CountofWeekDays;
RETURN @CountofWeekDays;
END
GO
Happy Coding
Todd P Payne
