March 20, 2017 at 8:02 am
We have a table with Service-Times (Time from to Time to AND a TicketNr). These times can also go over several days (Example: 2017-02-03 15:00 - 2017-02-06 12:30).
The task is to calculate the time difference in minutes, whereby no weekends (Sat, Su) OR holidays may be considered. The information about Weekend or Holidays comes from a Dim-Table (D_Time).
Which approach would you choose?
Thanks
Regards
Nicole
March 20, 2017 at 8:45 am
Interesting. Are you working only with calendar days? So a Friday 4:00pm ticket open until Tues 9:00am after a weekend and Monday holiday is 480 (Fri) + 540 (Tue) minutes?
If you can join to a table that has weekends and holidays, calculate the total minutes, subtract a day's worth of minutes for each holiday/weekend.
March 20, 2017 at 9:30 am
In very simplistic terms, I'd probably do something like this:CREATE TABLE #Dates (StartDate DateTime, EndDate DateTime);
INSERT INTO #Dates (StartDate, EndDate)
VALUES ('20170428 15:00:00', '20170504 10:00:00');
GO
SELECT DATEDIFF(MINUTE, StartDate, EndDate) - (NWD.NonWorkingDays * 24 * 60) AS WorkingDayMinutes
FROM #Dates D
CROSS APPLY (SELECT COUNT(*) AS NonWorkingDays
FROM DimDate DD
WHERE DD.Date BETWEEN CAST(D.StartDate AS date) AND CAST(D.EndDate AS date)
AND DD.[Working Day] = 0) NWD;
GO
DROP TABLE #Dates;
GO
This gives the answer 4020. As you can see, my Dates dimension has a field "Working Day". This is a BIT, 1 for Workling day, 0 for not. In this case, NWD includes 3 days, 29 April, 30 April and 01 May (which is a Public holiday this year in parts of the UK).
To check the maths, there are 9 hours left of the day on 28 Apr, and we get 10 hours into 04 May, totalling 19 hours (1140 minutes). We then have the days between (5 of them), making 7200 minutes. Only 2 of those days are working day, so 48 hours (2880 minutes). This gives 2880 + 1140 = 4020.
That isn't the actually formula that SQL uses, but i intentionally used a different method to display that the result is the same.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 20, 2017 at 2:29 pm
This is a perfect place to use testing to double check yourself. Pick a few tickets, then build a test like this (I use tSQLt - tsqlt.org)
EXEC tsqlt.NewTestClass @ClassName = N'tTimeTests';
GO
CREATE PROCEDURE [tTimeTests].[test calculate the time tests correctly]
AS
BEGIN
CREATE TABLE #Expected
( ticketnumber INT
, startdate DATETIME
, enddate DATETIME
, workingminutes INT)
INSERT #Expected
( startdate, enddate, workingminutes)
VALUES
( '20170428 15:00:00', '20170504 10:00:00', 4020 ),
( '20170322 20:00:00', '20170323 10:05:00', 845 )
-- act
SELECT ticketnumber, startdate, enddate
, minutecalculation
INTO #Actual
FROM tickets
-- assert
EXEC tsqlt.AssertEqualsTable @Expected = N'#Expected', @Actual = N'#Actual', @Message = N'incorrect calculation',
END
Put your query in the "Act" section with the into #Actual. Run this and see if whatever query you write works. You can change the results around (in the #Expected table) to get the correct items you expect. Will speed up your development and you can add cases as you need them.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply