Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.
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)
If you are experiencing a similar issue, please ask a related question
Join the community of 500,000 technology professionals and ask your questions.