Here are the winners of TSQL Challenge 2.
Antoine Gémis
Antoine Gémis lives in Toulouse, France. He is involved in software development over 15 years, mostly in networking, accounting and kiosk development.
Since 2008, he is an employee of Navocap as full time SQL developer. Antoine is working on "real-time" geolocation databases and you can find his blog here.
Rui Carvalho
Rui Carvalho is a senior developper on Sql Server and .Net mainly experienced in web applications. He work as consultant for a Microsoft experts company called Winwise in france. Rui worked in the past as a full time Sql developper specialised in sql optimisations and reporting for CRM applications and now mainly focus his time on .Net core technologies and Asp.net MVC. Architecture and software design are also part of his job.
He runs two blogs, one in French and one in English.
Tejas Shah
Tejas Shah, is very strong in .NET and MS SQL. He has 4+ years of Experience as a Web Developer. He is M.sc. (C.A. & I.T.) (Master Of Science in Computer Application and Information Technology). He is currently working as Team Leader at Ahmedabad, India.
You can find his blog here.
SOLVING THE CHALLENGE
Solving this challenge involves solving two logical hurdles. The first part of the problem is to generate a sequence of dates that falls within the given date values so that we can calculate the hours of each date. For example, to calculate the working hours between 6th March 2 PM and 9th March 11 AM, we need to look at each date that falls between this period and calculate the hours of each day. The following example illustrates this.
/*
From Date Day To Date WorkHrs
----------------- --- ----------------- -------
2009-Mar-06 14:00 Fri 2009-Mar-06 17:00 03:00
2009-Mar-07 08:00 Sat 2009-Mar-07 17:00 00:00
2009-Mar-08 08:00 Sun 2009-Mar-08 17:00 00:00
2009-Mar-09 08:00 Mon 2009-Mar-09 11:00 03:00
-----
06:00
-----
*/
So, the period between 2009-Mar-06 14:00 and 2009-03-09 11:00 gives us 6 working hours. We need to do such a calculation for each row we have in the table. So the key here is identifying the dates between given two values and then looking at the day (sunday, monday etc) and calculate the work hours accordingly.
The question now is, “how do we generate a sequence of dates that falls between a given date range. Well, there are a few options.
USING A CALENDAR TABLE
If you search on internet for “Calendar Table”, you will find many articles that explain the advantages of using a calendar table. Almost all databases that deal with business applications keep a calendar table to help perform various calculations. If your database has a calendar table, it can be used to solve the above problem.
Let us see, how we can make use of a calendar table to achieve this result. Let us build a calendar table for the purpose of this demonstration. I am inserting only 10 rows to the calendar table. But in a real life scenario, you might have a few years of data in your calendar table.
The following example builds a calendar table needed for this example
IF OBJECT_ID('Calendar','U') IS NOT NULL DROP TABLE Calendar
CREATE TABLE Calendar (dt DATETIME)
GO
DECLARE @FromDate DATETIME, @ToDate DATETIME
SELECT @FromDate = '2009-03-01', @ToDate = '2009-03-10'
WHILE @FromDate <= @ToDate BEGIN
INSERT INTO Calendar (dt) SELECT @FromDate
SELECT @FromDate = @FromDate + 1
END
SELECT * FROM Calendar
/*
dt
-----------------------
2009-03-01 00:00:00.000
2009-03-02 00:00:00.000
2009-03-03 00:00:00.000
2009-03-04 00:00:00.000
2009-03-05 00:00:00.000
2009-03-06 00:00:00.000
2009-03-07 00:00:00.000
2009-03-08 00:00:00.000
2009-03-09 00:00:00.000
2009-03-10 00:00:00.000
*/
Now, let us write a query that uses this calendar table to achieve the result that we needed.
DECLARE @t Table(FromDate DATETIME, ToDate DATETIME)
INSERT INTO @t (FromDate, ToDate) SELECT '2009-03-06 14:00', '2009-03-09 11:00'
SELECT
dt AS Date,
DATENAME(weekday, dt) AS wkday
FROM @t t
CROSS JOIN Calendar c
WHERE
dt BETWEEN DATEADD(d,0,DATEDIFF(d,0,FromDate))
AND
DATEADD(d,0,DATEDIFF(d,0,ToDate))
/*
Date wkday
----------------------- ------------------------------
2009-03-06 00:00:00.000 Friday
2009-03-07 00:00:00.000 Saturday
2009-03-08 00:00:00.000 Sunday
2009-03-09 00:00:00.000 Monday
*/
The above example demonstrated how to generate a sequence of dates between the given date values. Now let us apply a tiny logic and calculate the actual number of hours for each day. Let us generate the time period (business hours) on each day (between 8 am and 5 PM).
DECLARE @t Table(FromDate DATETIME, ToDate DATETIME)
INSERT INTO @t (FromDate, ToDate) SELECT '2009-03-06 14:00', '2009-03-09 11:00'
SELECT
dt AS Date,
LEFT(DATENAME(weekday, dt),3) AS wkday,
CASE
WHEN c.dt = DATEADD(d,0,DATEDIFF(d,0,FromDate)) THEN FromDate
ELSE DATEADD(hour,8,dt)
END AS StartTime,
CASE
WHEN c.dt = DATEADD(d,0,DATEDIFF(d,0,ToDate)) THEN ToDate
ELSE DATEADD(hour,17,dt)
END AS EndTime
FROM @t t
CROSS JOIN Calendar c
WHERE
dt BETWEEN DATEADD(d,0,DATEDIFF(d,0,FromDate))
AND
DATEADD(d,0,DATEDIFF(d,0,ToDate))
/*
Date wkday StartTime EndTime
----------------------- ----- ----------------------- -----------------------
2009-03-06 00:00:00.000 Fri 2009-03-06 14:00:00.000 2009-03-06 17:00:00.000
2009-03-07 00:00:00.000 Sat 2009-03-07 08:00:00.000 2009-03-07 17:00:00.000
2009-03-08 00:00:00.000 Sun 2009-03-08 08:00:00.000 2009-03-08 17:00:00.000
2009-03-09 00:00:00.000 Mon 2009-03-09 08:00:00.000 2009-03-09 11:00:00.000
*/
Look at the StartTime and EndTime. It shows the actual business hours on each day. Now, we can do DATEDIFF() function to get the differences between the two date values and ignore Sat and Sun. Then apply a SUM() over the DATEDIFF() function and you will get the total minutes. You can then convert this minutes to hours.
Note: The code given above is a simplified version of the final code, presented to explain one of the logical approaches to solve this problem. The above code is not tested against all the different combination of values.
USING A NUMBER TABLE
We saw, how to use a calendar table to solve the problem presented in this challenge. Now let us see how a number table can help solve this.
While a calendar table can help solve a number of date-time related problems, a number table can help solve date-time related problems as well as a wide range of other problems. I have used a number table to solve a number of string parsing problems.
Many of our databases have large number tables. If the range of numbers needed is small, I usually use the ‘undocumented’ table master..spt_values. You should use this only at your own risk :-). If you need a number table, it is always a good idea to create a number table instead of using spt_values table. However, I use master..spt_values for demonstrating code samples as it is pretty easy to use. (no setup needed).
The following code snippet shows a different version of the previous code that uses a number table.
DECLARE @t Table(FromDate DATETIME, ToDate DATETIME)
INSERT INTO @t (FromDate, ToDate) SELECT '2009-03-06 14:00', '2009-03-09 11:00'
SELECT
DATEADD(d,0,DATEDIFF(d,0,FromDate)) + Number AS Date,
LEFT(DATENAME(weekday, DATEADD(d,0,DATEDIFF(d,0,FromDate)) + Number),3) AS wkday,
CASE
WHEN DATEADD(d,0,DATEDIFF(d,0,FromDate)) + Number = DATEADD(d,0,DATEDIFF(d,0,FromDate))
THEN FromDate
ELSE DATEADD(hour,8,DATEADD(d,0,DATEDIFF(d,0,FromDate)) + Number)
END AS StartTime,
CASE
WHEN DATEADD(d,0,DATEDIFF(d,0,FromDate)) + Number = DATEADD(d,0,DATEDIFF(d,0,ToDate))
THEN ToDate
ELSE DATEADD(hour,17,DATEADD(d,0,DATEDIFF(d,0,FromDate)) + Number)
END AS EndTime
FROM @t t
CROSS JOIN master..spt_values c
WHERE
[type] = 'P'
AND
DATEADD(d,0,DATEDIFF(d,0,FromDate)) + Number BETWEEN DATEADD(d,0,DATEDIFF(d,0,FromDate))
AND
DATEADD(d,0,DATEDIFF(d,0,ToDate))
/*
Date wkday StartTime EndTime
----------------------- ----- ----------------------- -----------------------
2009-03-06 00:00:00.000 Fri 2009-03-06 14:00:00.000 2009-03-06 17:00:00.000
2009-03-07 00:00:00.000 Sat 2009-03-07 08:00:00.000 2009-03-07 17:00:00.000
2009-03-08 00:00:00.000 Sun 2009-03-08 08:00:00.000 2009-03-08 17:00:00.000
2009-03-09 00:00:00.000 Mon 2009-03-09 08:00:00.000 2009-03-09 11:00:00.000
*/
USING A RECURSIVE CTE
Another way of generating the sequence of dates is by using a recursive CTE. All the three winners of this challenge submitted their queries using a recursive CTE. This is a classic example that demonstrates the possibility of solving many more problems using recursive CTEs, against the common belief that recursive CTEs are only for generating hierarchical stuff.
Let us write a version of our previous queries using a recursive CTE.
DECLARE @t Table(FromDate DATETIME, ToDate DATETIME)
INSERT INTO @t (FromDate, ToDate) SELECT '2009-03-06 14:00', '2009-03-09 11:00'
;with cte as(
SELECT
DATENAME(weekday,Fromdate) AS Wkday,
FromDate,
ToDate
FROM @T
UNION ALL
SELECT
DATENAME(weekday,DATEADD(d, 1, DATEADD(dd,0, DATEDIFF(dd,0,c.Fromdate)))),
DATEADD(d, 1, DATEADD(dd,0, DATEDIFF(dd,0,c.Fromdate))) AS Fromdate,
c.ToDate
FROM @t t
INNER JOIN cte c ON
DATEADD(d, 1, DATEADD(dd,0, DATEDIFF(dd,0,c.FromDate))) <= c.ToDate
)
SELECT * FROM cte
/*
Wkday FromDate ToDate
------------------------------ ----------------------- -----------------------
Friday 2009-03-06 14:00:00.000 2009-03-09 11:00:00.000
Saturday 2009-03-07 00:00:00.000 2009-03-09 11:00:00.000
Sunday 2009-03-08 00:00:00.000 2009-03-09 11:00:00.000
Monday 2009-03-09 00:00:00.000 2009-03-09 11:00:00.000
*/
The above example demonstrates how to generate a range of dates between two given date values using a RECURSIVE CTE. Once the date values within the ranges are generated, we could apply a similar logic we discussed previously, to calculate the actual work hours between the values.
COMPLETE CODE LISTING
Now, let us see the listing of the complete code. Here is the entry submitted by Tejas.
DECLARE @t TABLE (ID INT IDENTITY, StartDate DATETIME, EndDate DATETIME)
INSERT INTO @t (StartDate, EndDate) SELECT '3/2/2009 8:00AM', '3/2/2009 3:00PM' --07:00
INSERT INTO @t (StartDate, EndDate) SELECT '3/1/2009 4:00PM', '3/3/2009 2:00 PM' --15:00
INSERT INTO @t (StartDate, EndDate) SELECT '2/26/2009 7:00AM', '2/26/2009 10:00PM' --09:00
INSERT INTO @t (StartDate, EndDate) SELECT '1/27/2009 9:15 AM', '1/27/2009 5:15 PM' --07:45
INSERT INTO @t (StartDate, EndDate) SELECT '1/17/2009 1:45 PM', '1/19/2009 7:45 AM' --00:00
INSERT INTO @t (StartDate, EndDate) SELECT '1/27/2009 9:15 PM', '1/28/2009 9:15 AM' --01:15
;with cte as(
SELECT ID,
StartDate AS OrgStartDate,
EndDate AS OrgEndDate,
StartDate,
EndDate
FROM @T
UNION ALL
SELECT t.ID,
c.OrgStartDate AS OrgStartDate,
c.OrgEndDate AS OrgEndDate,
DATEADD(d, 1, DATEADD(dd,0, DATEDIFF(dd,0,c.StartDate))) AS StartDate,
c.EndDate
FROM @t t
INNER JOIN cte c ON t.ID = c.ID
AND DATEADD(d, 1, DATEADD(dd,0, DATEDIFF(dd,0,c.StartDate))) <= c.EndDate
)
, cte2 AS(
SELECT ID,
OrgStartDate AS OrgStartDate,
OrgEndDate AS OrgEndDate,
DATENAME(dw,StartDate) AS DayName,
CASE
WHEN StartDate <= DATEADD(hh,8,DATEADD(dd,0, DATEDIFF(dd,0,c.StartDate)))
THEN DATEADD(hh,8,DATEADD(dd,0, DATEDIFF(dd,0,c.StartDate)))
ELSE StartDate
END AS StartDate,
CASE
WHEN EndDate <= DATEADD(hh,17,DATEADD(dd,0, DATEDIFF(dd,0,c.StartDate)))
THEN EndDate
ELSE DATEADD(hh,17,DATEADD(dd,0, DATEDIFF(dd,0,c.StartDate)))
END AS EndDate
from cte c
)
SELECT ID,
OrgStartDate AS StartDate,
OrgEndDate AS EndDate,
CAST(SUM(CASE
WHEN StartDate > EndDate THEN 0
ELSE DATEDIFF(mi,StartDate,EndDate)
END) / 60 AS INT) AS Hours,
CAST(SUM(CASE
WHEN StartDate > EndDate THEN 0
ELSE DATEDIFF(mi,StartDate,EndDate)
END) % 60 AS INT) AS Minutes
from cte2
WHERE DAYNAME NOT IN('Saturday', 'SUNDAY')
GROUP BY ID, OrgStartDate, OrgEndDate
Given below is the entry submitted by antoine
-- antoine.gemis@gmail.com --
DECLARE @t TABLE (ID INT IDENTITY, StartDate DATETIME, EndDate DATETIME)
SET DATEFORMAT MDY
SET DATEFIRST 1
INSERT INTO @t (StartDate, EndDate) SELECT '3/5/2009 18:00PM', '3/10/2009 7:00AM' --18:00
INSERT INTO @t (StartDate, EndDate) SELECT '3/2/2009 8:00AM', '3/2/2009 3:00PM' --07:00
INSERT INTO @t (StartDate, EndDate) SELECT '3/1/2009 4:00PM', '3/3/2009 2:00 PM' --15:00
INSERT INTO @t (StartDate, EndDate) SELECT '2/26/2009 7:00AM', '2/26/2009 10:00PM' --09:00
INSERT INTO @t (StartDate, EndDate) SELECT '1/27/2009 9:15 AM', '1/27/2009 5:15 PM' --07:45
INSERT INTO @t (StartDate, EndDate) SELECT '1/17/2009 1:45 PM', '1/19/2009 7:45 AM' --00:00
INSERT INTO @t (StartDate, EndDate) SELECT '1/27/2009 9:15 PM', '1/28/2009 9:15 AM' --01:15
-- CTE pour fabriquer la sequence de jours concernés
;WITH CTE_EXTRACTWORKDAY (ID, StartDate, EndDate) AS
(
SELECT
ID, StartDate, EndDate
FROM @t
group by ID, startdate, enddate
UNION ALL
SELECT ID, DATEADD(day, 1, StartDate), EndDate
FROM CTE_EXTRACTWORKDAY
WHERE StartDate < EndDate
),
-- On extrait les jours de travail potentiels
CTE_WORKDAY (ID, WorkDay) AS
(
SELECT ID, DATEADD(d, 0, DATEDIFF(DAY, 0, startdate)) AS WorkDay
FROM CTE_EXTRACTWORKDAY
WHERE StartDate < EndDate
UNION
SELECT ID, DATEADD(d, 0, DATEDIFF(DAY, 0, enddate)) AS WorkDay
FROM CTE_EXTRACTWORKDAY
WHERE StartDate < EndDate
),
-- Calcul de la différenc en minute entre les bornes de début de fin de travail au bureau.
CTE_MORECLEAR (WorkDay, ID, StartDate, EndDate, WorkedMinutes) AS
(
SELECT CTE_WORKDAY.WorkDay,
A.ID,
A.StartDate,
A.EndDate,
CASE DATEPART(WEEKDAY, WORKDAY) -- On n'ouvre pas le week-end.
WHEN 6 THEN 0
WHEN 7 THEN 0
ELSE
CASE WHEN
DATEDIFF(MINUTE,
CASE WHEN StartDate < DATEADD(HOUR, 8, CTE_WORKDAY.WorkDay) THEN
DATEADD(HOUR, 8, CTE_WORKDAY.WorkDay)
ELSE
StartDate
END,
CASE WHEN EndDate > DATEADD(HOUR, 17, CTE_WORKDAY.WorkDay) THEN
DATEADD(HOUR, 17, CTE_WORKDAY.WorkDay)
ELSE
EndDate
END)
< 0 THEN 0
ELSE
DATEDIFF(MINUTE,
CASE WHEN StartDate < DATEADD(HOUR, 8, CTE_WORKDAY.WorkDay) THEN
DATEADD(HOUR, 8, CTE_WORKDAY.WorkDay)
ELSE
StartDate
END,
CASE WHEN EndDate > DATEADD(HOUR, 17, CTE_WORKDAY.WorkDay) THEN
DATEADD(HOUR, 17, CTE_WORKDAY.WorkDay)
ELSE
EndDate
END)
END
END AS WorkedMinutes
FROM CTE_WORKDAY
LEFT JOIN @t A ON A.ID = CTE_WORKDAY.ID
)
SELECT StartDate, EndDate,
SUM(WorkedMinutes) / 60 as Hours,
SUM(WorkedMinutes) -(SUM(WorkedMinutes) / 60 * 60) AS Minutes
FROM CTE_MORECLEAR
GROUP BY ID, StartDate, EndDate
ORDER BY ID
Finally, here is the code of Rui
DECLARE @t TABLE (ID INT IDENTITY, StartDate DATETIME, EndDate DATETIME)
SET DATEFORMAT MDY
INSERT INTO @t (StartDate, EndDate) SELECT '3/2/2009 8:00AM', '3/2/2009 3:00PM' --07:00
INSERT INTO @t (StartDate, EndDate) SELECT '3/1/2009 4:00PM', '3/3/2009 2:00 PM' --15:00
INSERT INTO @t (StartDate, EndDate) SELECT '2/26/2009 7:00AM', '2/26/2009 10:00PM' --09:00
INSERT INTO @t (StartDate, EndDate) SELECT '1/27/2009 9:15 AM', '1/27/2009 5:15 PM' --07:45
INSERT INTO @t (StartDate, EndDate) SELECT '1/17/2009 1:45 PM', '1/19/2009 7:45 AM' --00:00
INSERT INTO @t (StartDate, EndDate) SELECT '1/27/2009 9:15 PM', '1/28/2009 9:15 AM' --01:15
-- Build the range of days between start and end with additional helper calcultations
;WITH BUILD_RANGES AS
(
-- select the exact start date based on business hours
SELECT
T.ID
,StartDate =
CASE
WHEN DATEPART(HOUR,T.StartDate) < 8
THEN DATEADD(HOUR,8,DATEADD(DAY,0,DATEDIFF(DAY,0,T.StartDate)))
WHEN DATEPART(HOUR,T.StartDate) > 17
THEN DATEADD(HOUR,17,DATEADD(DAY,0,DATEDIFF(DAY,0,T.StartDate)))
ELSE T.StartDate
END
,T.EndDate
,BusinessDay = CASE
WHEN DATEPART(DW,T.StartDate) IN (6,7) OR T.EndDate < T.StartDate
THEN 0 ELSE 1
END
FROM @t T
UNION ALL
-- Select the other dates til the end
SELECT A.ID
,StartDate = DATEADD(HOUR,8,DateAdd(day,1,DATEADD(DAY,0,DATEDIFF(DAY,0,A.StartDate))))
,A.EndDate
,BusinessDay = CASE
WHEN DATEPART(DW,DateAdd(day,1,A.StartDate)) IN (6,7) THEN 0
ELSE 1
END
FROM BUILD_RANGES A
INNER JOIN @t T ON A.ID = T.id
WHERE A.StartDate < T.EndDate
)
-- do final computation
SELECT ID,StartDate,EndDate,
HOURS = SUM(DATEDIFF(MINUTE,ComputedStart,ComputedEnd))/60,
MINUTES = SUM(DATEDIFF(MINUTE,ComputedStart,ComputedEnd))%60
FROM (
-- select final values with enddate based on business hours
SELECT O.Id
,ComputedStart = C.StartDate
,ComputedEnd =
CASE WHEN DATEADD(DAY,0,DATEDIFF(DAY,0,C.StartDate))
= DATEADD(DAY,0,DATEDIFF(DAY,0,O.EndDate))
THEN
CASE
WHEN DATEPART(HOUR,C.EndDate) < 8
THEN DATEADD(HOUR,8,DATEADD(DAY,0,DATEDIFF(DAY,0,C.EndDate)))
WHEN DATEPART(HOUR,C.EndDate) > 16
THEN DATEADD(HOUR,17,DATEADD(DAY,0,DATEDIFF(DAY,0,C.EndDate)))
ELSE CASE WHEN O.StartDate > O.EndDate
THEN C.StartDate ELSE C.EndDate END
END
ELSE
DATEADD(HOUR,17,DATEADD(DAY,0,DATEDIFF(DAY,0,C.StartDate)))
END
,BusinessDay
,O.StartDate,O.EndDate
FROM BUILD_RANGES C
FULL JOIN @t O ON C.ID = O.ID
) FINAL_DATA
WHERE 1=1
AND (BusinessDay = 1 OR StartDate > EndDate)
AND DATEADD(DAY,0,DATEDIFF(DAY,0,ComputedStart))
<= DATEADD(DAY,0,DATEDIFF(DAY,0,EndDate))
GROUP BY ID,StartDate,EndDate
ORDER BY ID
I would like to thank all of you who participated as well as those who attempted to solve this challenge. I will post the next challenge soon.