So if an individual worked 08:00 - 16:00, you would only count 09:00-16:00?
When dealing with day-of-week calculations, it's important to take account of the current DATEFIRST settings. This query will always correctly exclude weekend days, using @@DATEFIRST to account for any possible setting for the first day of the week.
SELECT *
FROM your_table
WHERE ((DATEPART(dw, date_created) + @@DATEFIRST) % 7) NOT IN (0, 1)
You can get the difference between the two dates to whatever resolution you want (in your example, minutes):
DATEDIFF(minute, @start_date, @end_date)
-- Set things up before we get started
--------------------------------------
DECLARE @WorkTimeStart TINYINT,
@WorkTimeEnd TINYINT,
@WorkDayOfWeekStart TINYINT,
@WorkDayOfWeekEnd TINYINT
DECLARE @StartDate DATETIME,
@EndDate DATETIME
CREATE TABLE #WorkingHours (
EvaluateTime DATETIME,
IsWorktime BIT DEFAULT(0)
)
--------------------------------------
SET @WorkTimeStart = 7 --7AM
SET @WorkTimeEnd = 16 --4PM hour (4-5PM count as working)
SET @WorkDayOfWeekStart = 2 --Monday
SET @WorkDayOfWeekEnd = 6 --Friday
SET @StartDate = '2000-01-01 00:00:00'
SET @EndDate = '2020-12-31 23:59:59'
--------------------------------------
-- Built the list of timestamps we're working with
;WITH numberlist(number)
AS (SELECT RANK() over(order by c1.object_id,
c1.column_id,
c2.object_id,
c2.column_id)
from sys.columns c1
cross
join sys.columns c2)
INSERT INTO #WorkingHours (EvaluateTime)
SELECT DATEADD(hh, number-1, @StartDate)
FROM numberlist
WHERE DATEADD(hh, number-1, @StartDate) <= @EndDate
-- Set the times to worktime if they match criteria
UPDATE #WorkingHours
SET IsWorktime = CASE WHEN (DATEPART(dw, EvaluateTime)
BETWEEN @WorkDayOfWeekStart
AND @WorkDayOfWeekEnd)
AND
(DATEPART(hh, EvaluateTime)
BETWEEN @WorkTimeStart
AND @WorkTimeEnd) THEN 1
ELSE 0
END
SELECT EmployeeID,
StartTime,
EndTime,
SUM( CONVERT(INT, wh.WorkTime)) as WorkHoursBetweenDates
FROM TimeLogs t
JOIN #WorkingHours wh
ON wh.EvaluateTime BETWEEN t.StartTime
AND t.EndTime
GROUP BY EmployeeID,
StartTime,
EndTime
CREATE FUNCTION dbo.CalcWorkMinutes (
@start_date datetime,
@end_date datetime
)
--SELECT dbo.CalcWorkMinutes('20130104 14:30', '20130107 10:45')
RETURNS int
AS
BEGIN
DECLARE @minutes_worked int
;WITH times AS (
SELECT
DATEDIFF(DAY, @start_date, @end_date) AS #days,
DATEADD(DAY, -DATEDIFF(DAY, 0, @start_date), @start_date) AS start_time,
DATEADD(DAY, -DATEDIFF(DAY, 0, @end_date), @end_date) AS end_time
),
days AS (
SELECT 0 AS day# UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
)
SELECT @minutes_worked = SUM(minutes_worked)
FROM (
-- starting day
SELECT
CASE
WHEN DATEDIFF(DAY, 0, @start_date) % 7 IN (5, 6) OR start_time > '17:00' THEN 0 --Sat/Sun/After-hours
WHEN #days = 0 AND end_time < '09:00' THEN 0 --Before-hours
ELSE DATEDIFF(MINUTE, CASE WHEN start_time < '09:00' THEN '09:00' ELSE start_time END,
CASE WHEN #days > 0 OR end_time > '17:00' THEN '17:00' ELSE end_time END)
END AS minutes_worked
FROM times
UNION ALL
-- inbetween days
SELECT
CASE
WHEN DATEDIFF(DAY, 0, DATEADD(DAY, day#, @start_date)) % 7 IN (5, 6) THEN 0 --Sat/Sun
ELSE 8 * 60
END AS minutes_worked
FROM times
CROSS JOIN days
WHERE
day# BETWEEN 1 AND (#days - 1)
UNION ALL
-- ending day
SELECT
CASE
WHEN DATEDIFF(DAY, 0, @start_date) % 7 IN (5, 6) OR end_time < '09:00' THEN 0 --Sat/Sun/Before-hours
WHEN #days = 0 AND start_time > '17:00' THEN 0 --After-hours
ELSE DATEDIFF(MINUTE, '09:00', CASE WHEN end_time > '17:00' THEN '17:00' ELSE end_time END)
END AS minutes_worked
FROM times
) AS derived
RETURN @minutes_worked
END --FUNCTION
create table #tempvals
(
personid int,
starttime datetime,
endtime datetime
)
insert into #tempvals
select 1, '03 jan 2013 08:00', '03 jan 2013 08:45'
union all
select 2, '03 jan 2013 08:00', '03 jan 2013 09:45'
union all
select 3, '03 jan 2013 11:00', '03 jan 2013 11:45'
union all
select 4, '03 jan 2013 11:00', '03 jan 2013 16:45'
union all
select 5, '03 jan 2013 11:00', '03 jan 2013 17:45'
union all
select 6, '03 jan 2013 11:00', '04 jan 2013 11:00'
union all
select 7, '03 jan 2013 13:00', '07 jan 2013 09:45'
union all
select 8, '05 jan 2013 10:00', '06 jan 2013 09:45'
union all
select 9, '03 jan 2013 16:00', '14 jan 2013 09:45'
select * from #tempvals
ALTER FUNCTION dbo.CalcWorkMinutes (
@start_date datetime,
@end_date datetime
)
--SELECT dbo.CalcWorkMinutes('20130104 14:30', '20130107 10:45')
RETURNS int
AS
BEGIN
DECLARE @minutes_worked int
;WITH times AS (
SELECT
DATEDIFF(DAY, @start_date, @end_date) AS #days,
DATEADD(DAY, -DATEDIFF(DAY, 0, @start_date), @start_date) AS start_time,
DATEADD(DAY, -DATEDIFF(DAY, 0, @end_date), @end_date) AS end_time
),
days AS (
SELECT 0 AS day# UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
)
SELECT @minutes_worked = SUM(minutes_worked)
FROM (
-- starting day
SELECT
CASE
WHEN DATEDIFF(DAY, 0, @start_date) % 7 IN (5, 6) OR start_time > '17:00' THEN 0 --Sat/Sun/After-hours
WHEN #days = 0 AND end_time < '09:00' THEN 0 --Before-hours
ELSE DATEDIFF(MINUTE, CASE WHEN start_time < '09:00' THEN '09:00' ELSE start_time END,
CASE WHEN #days > 0 OR end_time > '17:00' THEN '17:00' ELSE end_time END)
END AS minutes_worked
FROM times
UNION ALL
-- inbetween days
SELECT
CASE
WHEN DATEDIFF(DAY, 0, DATEADD(DAY, day#, @start_date)) % 7 IN (5, 6) THEN 0 --Sat/Sun
ELSE 8 * 60
END AS minutes_worked
FROM times
CROSS JOIN days
WHERE
day# BETWEEN 1 AND (#days - 1)
UNION ALL
-- ending day
SELECT
CASE
WHEN DATEDIFF(DAY, 0, @start_date) % 7 IN (5, 6) OR end_time < '09:00' THEN 0 --Sat/Sun/Before-hours
WHEN #days = 0 AND start_time > '17:00' THEN 0 --After-hours
ELSE DATEDIFF(MINUTE, '09:00', CASE WHEN end_time > '17:00' THEN '17:00' ELSE end_time END)
END AS minutes_worked
FROM times
[b] WHERE --add bolded lines (rest of func is the same)
#days > 0[/b]
) AS derived
RETURN @minutes_worked
END --FUNCTION
;with cte_dates as
( select personid, starttime, endtime
,case when datepart(hour,starttime) < 9 then dateadd(hour,9,dateadd(day,datediff(day,0,starttime),0))
when datepart(hour,starttime) >= 17 then dateadd(hour,9,dateadd(day,datediff(day,0,starttime + 1),0))
else starttime
end as calc_starttime
,case when datepart(hour,endtime) < 9 then dateadd(hour,9,dateadd(day,datediff(day,0,endtime),0))
when datepart(hour,endtime) >= 17 then dateadd(hour,17,dateadd(day,datediff(day,0,endtime),0))
when datediff(day,starttime, endtime) > 0 then dateadd(hour,17,dateadd(day,datediff(day,0,starttime),0))
else endtime
end as calc_endtime
,datename(dw,starttime) as dow
,dateadd(day,datediff(day,0,starttime),0) as startdate
,dateadd(day,datediff(day,0,endtime),0) as enddate
from #tempvals
--where starttime >= @starttime
--and endtime <= @endtime
union all
select personid,starttime, endtime
,dateadd(hour,9,dateadd(day,datediff(day,0,calc_starttime + 1),0))
,case when startdate+1 = enddate then endtime
else dateadd(hour,17,dateadd(day,datediff(day,0,calc_starttime + 1),0))
end
,datename(dw,calc_starttime+1), startdate + 1, enddate
from cte_dates
where startdate + 1 <= enddate
)
select personid, starttime, endtime, sum(case when dow in ('saturday','sunday') then 0 else datediff(minute,calc_starttime,calc_endtime) end) as minutes
from cte_dates D
group by personid, starttime, endtime
CREATE FUNCTION udf_work_minutes (@starttime datetime, @endtime datetime)
RETURNS INT
as
BEGIN
declare @minutes int
;with cte_dates as
( select starttime, endtime
,case when datepart(hour,starttime) < 9 then dateadd(hour,9,dateadd(day,datediff(day,0,starttime),0))
when datepart(hour,starttime) >= 17 then dateadd(hour,9,dateadd(day,datediff(day,0,starttime + 1),0))
else starttime
end as calc_starttime
,case when datepart(hour,endtime) < 9 then dateadd(hour,9,dateadd(day,datediff(day,0,endtime),0))
when datepart(hour,endtime) >= 17 then dateadd(hour,17,dateadd(day,datediff(day,0,endtime),0))
when datediff(day,starttime, endtime) > 0 then dateadd(hour,17,dateadd(day,datediff(day,0,starttime),0))
else endtime
end as calc_endtime
,datename(dw,starttime) as dow
,dateadd(day,datediff(day,0,starttime),0) as startdate
,dateadd(day,datediff(day,0,endtime),0) as enddate
from (select @starttime as starttime, @endtime as endtime) a
union all
select starttime, endtime
,dateadd(hour,9,dateadd(day,datediff(day,0,calc_starttime + 1),0))
,case when startdate+1 = enddate then endtime
else dateadd(hour,17,dateadd(day,datediff(day,0,calc_starttime + 1),0))
end
,datename(dw,calc_starttime+1), startdate + 1, enddate
from cte_dates
where startdate + 1 <= enddate
)
select @minutes = sum(case when dow in ('saturday','sunday') then 0 else datediff(minute,calc_starttime,calc_endtime) end)
from cte_dates D
RETURN (@minutes)
END
GO
-- then to use it against the #tempvals sample
SELECT *, dbo.udf_work_minutes(starttime, endtime) as minutes
FROM #tempvals t
create FUNCTION udf_work_minutes_TVF (@starttime datetime, @endtime datetime)
returns table
as
return (
with cte_dates as
( select starttime, endtime
,case when datepart(hour,starttime) < 9 then dateadd(hour,9,dateadd(day,datediff(day,0,starttime),0))
when datepart(hour,starttime) >= 17 then dateadd(hour,9,dateadd(day,datediff(day,0,starttime + 1),0))
else starttime
end as calc_starttime
,case when datepart(hour,endtime) < 9 then dateadd(hour,9,dateadd(day,datediff(day,0,endtime),0))
when datepart(hour,endtime) >= 17 then dateadd(hour,17,dateadd(day,datediff(day,0,endtime),0))
when datediff(day,starttime, endtime) > 0 then dateadd(hour,17,dateadd(day,datediff(day,0,starttime),0))
else endtime
end as calc_endtime
,datename(dw,starttime) as dow
,dateadd(day,datediff(day,0,starttime),0) as startdate
,dateadd(day,datediff(day,0,endtime),0) as enddate
from (select @starttime as starttime, @endtime as endtime) a
union all
select starttime, endtime
,dateadd(hour,9,dateadd(day,datediff(day,0,calc_starttime + 1),0))
,case when startdate+1 = enddate then endtime
else dateadd(hour,17,dateadd(day,datediff(day,0,calc_starttime + 1),0))
end
,datename(dw,calc_starttime+1), startdate + 1, enddate
from cte_dates
where startdate + 1 <= enddate
)
select sum(case when dow in ('saturday','sunday') then 0 else datediff(minute,calc_starttime,calc_endtime) end) as minutes
from cte_dates D
)
GO
-- and again, to use it
SELECT *
FROM #tempvals t
CROSS APPLY dbo.udf_work_minutes_TVF(t.starttime,t.endtime)
select sum(case when dow in ('saturday','sunday') then 0 else datediff(minute,calc_starttime,calc_endtime) end) as minutes
from cte_dates D option (maxrecursion 0)
Incorrect syntax near the keyword 'option'
select sum(case when dow in ('saturday','sunday') then 0 else datediff(minute,calc_starttime,calc_endtime) end) as minutes
from cte_dates D option (maxrecursion 0)
Title | # Comments | Views | Activity |
---|---|---|---|
SQL Server 2008 R2 - Execution Plan | 3 | 28 | |
encyps queries mssql | 15 | 26 | |
Updating ms sql with special characters | 8 | 19 | |
Need help writing 2 Insert Query Scripts in SQL Server 2012 | 4 | 19 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
16 Experts available now in Live!