declare @start as datetime
declare @end as datetime
declare @minutes as int
select @start= '4-jan-2010 10:00:00 am'
select @end='4-jan-2010 12:00:00 pm'
--Convert the start time to the opening hour start, or Sunday for saturday
select @start=
CASE
WHEN bh.dayofweek IN ('sunday' )
AND CONVERT (VARCHAR(20), @start, 108) < CONVERT (VARCHAR(20), bh.startTime, 108)
THEN CONVERT (DATETIME, CONVERT (VARCHAR(8), @start, 112)
+ ' ' + CONVERT (VARCHAR(20), bh.startTime, 108))
WHEN datename(weekday, @start) IN ('saturday' )
THEN CONVERT (DATETIME, CONVERT (VARCHAR(8), dateadd(dd, 1, @start), 112)
+ ' ' + CONVERT (VARCHAR(20), bh.startTime, 108))
WHEN CONVERT (VARCHAR(20), @start, 108) < CONVERT (VARCHAR(20), bh.startTime, 108)
THEN CONVERT (DATETIME, CONVERT (VARCHAR(8), @start, 112)
+ ' ' + CONVERT (VARCHAR(20), bh.startTime, 108))
ELSE @start
END
from
businesshours bh
where bh.dayofweek=datename(weekday,@start)
--totals the number of minutes between start and end dateTime
if datediff(dd,@start,@end)=0
begin
select @minutes=datediff(mi,@start,@end)
end
else
begin
;with dates (logTime,rowid) as
(select DATEADD(dd,H.i*100 + T.i*10 + U.i,@start) as startTime,
ROW_NUMBER() OVER (ORDER BY DATEADD(dd,H.i*100 + T.i*10 + U.i,@start) ASC) AS ROWID
FROM integers AS H
CROSS
JOIN integers AS T
CROSS
JOIN integers AS U
where convert(varchar(8),DATEADD(dd,H.i*100 + T.i*10 + U.i,@start),112)<=convert(varchar(8),@end,112)
),
totalMi (mi) as(
select case
when rowid =(select min(rowid) from dates)
THEN datediff(mi,@start,CONVERT (DATETIME, CONVERT (VARCHAR(8), @start, 112)
+ ' ' + CONVERT (VARCHAR(20), bh.endtime, 108)))
when rowid=(select max(rowid) from dates)
THEN datediff(mi,CONVERT (DATETIME, CONVERT (VARCHAR(8), @end, 112)
+ ' ' + CONVERT (VARCHAR(20), bh.starttime, 108)),@end)
else
datediff(mi,bh.starttime,bh.endtime)
end
from dates d
inner join businessHours bh on datename(weekday,d.logtime)=bh.dayOfWeek
)
select @minutes=sum(mi) from totalmi
End
