September 4, 2014 at 8:27 am
I need to be able to add minutes to a datetime value, which only cover working hours.
I have a holiday table as below:
Examples: (dd/MM/yyyy hh:mm)
Date..........Description
01/01/2015 New Years Day
26/12/2014 Boxing Day
25/12/2014 Christmas Day
25/08/2014 August Bank Holiday
Our Business hours are 08:00-18:00 Mon-Fri (unless the day is in the holiday table)
Start Date............Minute to Add......Expected outcome
01/09/2014 10:00........30...................01/09/2014 10:30
01/09/2014 17:00........65...................02/09/2014 08:05
29/08/2014 17:00........65...................01/09/2014 08:05
22/08/2014 17:00........65...................26/08/2014 08:05
31/08/2014 02:30........65...................01/08/2014 09:05
01/09/2014 19:00........65...................02/08/2014 09:05
01/09/2014 10:00........3005...............08/09/2014 10:05
22/08/2014 17:00........3005...............01/09/2014 17:05
I have tried to create a function to do this (fn_pp_AddMinutesWithinWorkingHours(@StartDate,@Minutes)) but I am unable to come up with a solution which factors in everything correctly
Any assistance you can provide would be very gratefully received
September 4, 2014 at 9:43 am
Something like this might work. It's probably not the most efficient. It's not tested because of the lack of DDL and sample data. You need a numbers table (here called Numbers and with a column called TheNumber). If you don't have one, you can make one or create one on the fly in the query.
DECLARE @StartDate datetime
SELECT
@StartDate = MIN(StartDate)
FROM
MyTable
WHERE
MinutetoAdd IS NOT NULL;
WITH Mins AS (
-- Gets a sequence of minutes starting with the earliest
-- date in your table. Need to make sure you have enough
-- numbers in your Numbers table for this!
SELECT
DATEADD(mi,@StartDate,TheNumber) TheMinute
,TheNumber
FROM
Numbers
)
, MinstoInclude AS (
-- Gets rid of weekends, bank holidays,
-- evenings and early mornings
SELECT
m.TheMinute
,m.TheNumber
FROM
Mins m
LEFT JOIN
Holidays h
ON
CAST(m.TheMinute AS date) = h.HolDate
WHERE
h.HolDate IS NULL
AND
DATEPART(hh,TheMinute) BETWEEN 8 AND 17
AND -- sorry but this part is pseudo code!
DATEPART(dw,TheMinute) NOT IN ('Sat','Sun')
)
SELECT
t.StartDate
,t.MinutetoAdd
,m2.MinuteNo
FROM
MinstoInclude m1
JOIN
MyTable t ON m.TheMinute = t.StartDate
JOIN
MinstoInclude m2 ON m1.TheNumber = m2.TheNumber + t.MinutestoAdd
John
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply