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 ;
(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);
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;