I need to calculate the duration between 2 datetimes excluding breaks. I calculate total time using for example if t1 is '02/12/2018 14:18:29' and t2 is '02/14/2018 14:18:29'
Now if I want to exclude the time there were breaks like if 14:30 to 14:45 was a break every day, how do I create my query.
select datediff(minute, t1, t2) - (select case when cast(t1 as time) > '14:30:00:000' and cast(t2 as time) < '14:45:00:000' then 15 else 0 end as breaks)
But I don't seem to get the right answersqlsql-server