StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I'm assuming that you need a a function to add minutes to an escalation time, but only during working hours.</p> <p>You could calculate your minutes from your first table by using something like</p> <pre><code>Select SUM(Minute) from auxiliary where Time <= CURRENT_TIMESTAMP </code></pre> <p>The following function will do that, skipping any time that's outside @SCStartHours and @SCEndHours. And also outside Saturday and Sunday:</p> <p>The function would be used like so:</p> <p>Adding 60 "working minutes" to an OPEN_TIME:</p> <pre><code>select AddWorkingMinutes(OPEN_TIME, 60, 9,17) create Function AddWorkingMinutes(@EscalateOnDate datetime, @escalationminutes int, @SCStartHours int, @SCEndHours int) returns datetime as begin declare @workminutesperday int = (@SCEndHours - @SCStartHours)*60 declare @workdays int = @escalationminutes/@workminutesperday declare @minutes int = @escalationminutes - @workminutesperday * @workdays -- If the date falls out of hours then move it forward to the next earliest period while DATENAME(weekday, @EscalateOnDate ) = 'Saturday' OR DATENAME(weekday, @EscalateOnDate ) = 'Sunday' OR DATEPART(hour, @EscalateOnDate ) >= @SCEndHours begin set @EscalateOnDate = DATEADD(dd,1,@EscalateOnDate ) set @EscalateOnDate = DATEADD(hh,@SCStartHours,DATEADD(dd,0, DATEDIFF(dd,0, @EscalateOnDate ))) end while DATEPART(hour,@EscalateOnDate ) < @SCStartHours begin set @EscalateOnDate = DATEADD(hh,@SCStartHours,DATEADD(dd,0, DATEDIFF(dd,0, @EscalateOnDate ))) end --now check if we have enough minutes in the day remaining, if not add a day and take the remaining minutes off. declare @minutesremaining int = DATEDIFF(minute, @EscalateOnDate , DATEADD(hh,@SCEndHours,DATEADD(dd,0, DATEDIFF(dd,0, @EscalateOnDate )))) if @minutesremaining < @minutes begin set @minutes = @minutes - @minutesremaining set @workdays = @workdays + 1 set @EscalateOnDate = DATEADD(hh,@SCStartHours,DATEADD(dd,0, DATEDIFF(dd,0, @EscalateOnDate ))) end return DATEADD(minute,@minutes,DATEADD(day, (@workdays % 5) + CASE ((@@DATEFIRST + DATEPART(weekday, @EscalateOnDate ) + (@workdays % 5)) % 7) WHEN 0 THEN 2 WHEN 1 THEN 1 ELSE 0 END, DATEADD(week, (@workdays / 5), @EscalateOnDate ))) end </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload