April 11, 2017 at 7:02 am
Hi All,
I know this is old topic and there are thousand of discussions on this topics already.
What I want to accomplish here is to calculate business hours between two dates. I don't have to worry about weekend and/or holidays.
My business working hours are 8:30AM to 9:00PM Monday - Saturday. Sunday is 11:00AM to 7:00PM
My approach to accomplish this is really based on what everybody has already suggested.
Here are steps I’ve taken to accomplish:
Icreated a temp table to store my test data
DECLARE @tTABLE(id INT,StartDt DATETIME,EndDt DATETIME);
INSERT INTO@t VALUES(7,'2017-04-01 18:11:06.000','2017-04-04 17:18:15.000');
INSERT INTO@t VALUES(1,'2017-04-09 15:04:47.000','2017-04-09 17:18:51.000');
INSERT INTO@t VALUES(2,'2017-04-03 18:36:57.000','2017-04-05 16:51:57.000');
INSERT INTO@t VALUES(7,'2017-04-01 16:46:33.000','2017-04-04 12:29:15.000');
INSERT INTO@t VALUES(1,'2017-04-02 20:14:22.000','2017-04-03 09:36:57.000');
Icreated a CTE to stored working hours for each business day because I do nothave permission to create table or create function/stored procedure.
;WITH CTE_Business_Hours AS
(
SELECT 1 [DayOfWeek],
'19000101 11:00' BusinessDayStartHour,
'19000101 19:00' BusinessDayFinishHour-- Sunday
UNION ALL
SELECT 2 [DayOfWeek],
'19000101 08:30' BusinessDayStartHour,
'19000101 21:00' BusinessDayFinishHour-- Monday
UNION ALL
SELECT 3 [DayOfWeek],
'19000101 08:30' BusinessDayStartHour,
'19000101 21:00' BusinessDayFinishHour-- Tuesday
UNION ALL
SELECT 4 [DayOfWeek],
'19000101 08:30' BusinessDayStartHour,
'19000101 21:00' BusinessDayFinishHour-- Wednesday
UNION ALL
SELECT 5 [DayOfWeek],
'19000101 08:30' BusinessDayStartHour,
'19000101 21:00' BusinessDayFinishHour-- Thursday
UNION ALL
SELECT 6 [DayOfWeek],
'19000101 08:30' BusinessDayStartHour,
'19000101 21:00' BusinessDayFinishHour-- Friday
UNION ALL
SELECT 7 [DayOfWeek],
'19000101 08:30' BusinessDayStartHour,
'19000101 21:00' BusinessDayFinishHour– Saturday
)
SELECT
t.id [DayOfWeek],
t.StartDt StartDate,
t.EndDt EndDate,
DATEPART(DW, t.StartDt) DayOfWeek,
NumberOfHours =CASE WHEN DATEADD(DAY,DATEDIFF(DAY,0,StartDt),0) < DATEADD(DAY,DATEDIFF(DAY,0,EndDt),0) THEN
DATEDIFF(MINUTE,DATEADD(DAY,-DATEDIFF(DAY,0,StartDt),StartDt),BusinessDayFinishHour) + (DATEDIFF(DAY,StartDt,EndDt)-1) * 1440 +
DATEDIFF(MINUTE,BusinessDayStartHour,DATEADD(DAY,-DATEDIFF(DAY,0,EndDt),EndDt))
ELSE
DATEDIFF(MINUTE,StartDt,EndDt)
END / 60.0
FROM @tt
INNER JOIN CTE_Business_Hours hrsON DATEPART(DW, t.StartDt) = hrs.[DayOfWeek]
Iran my query. This is the result I am getting. As you can see, I have a negative result which is incorrect.
DayOfWeekStartDate EndDate DayOfWeek NumberOfHours
1 2017-04-0915:04:47.000 2017-04-09 17:18:51.000 1 2.233333
1 2017-04-0220:14:22.000 2017-04-03 09:36:57.000 1 -2.633333
2 2017-04-0318:36:57.000 2017-04-05 16:51:57.000 2 34.750000
7 2017-04-0116:46:33.000 2017-04-04 12:29:15.000 7 56.216666
7 2017-04-0118:11:06.000 2017-04-04 17:18:15.000 7 59.616666
Can you please help. Thank you in advance.
April 11, 2017 at 7:17 am
Be careful with that query. It's sensitive to local settings. As an illustration, try running this:SET LANGUAGE 'us_english';
SELECT DATEPART(dw,CURRENT_TIMESTAMP);
SET LANGUAGE 'British';
SELECT DATEPART(dw,CURRENT_TIMESTAMP);
I don't know whether that's the cause of your negative result, but you need to correct it anyway, even if you think you're always going to have the same language setting.
John
April 11, 2017 at 8:26 am
Thom A - Tuesday, April 11, 2017 8:21 AMOne possible problem I could see is if your business opening hours change. Is this likely, and if so, how would you cater for it?
The possibility of opening or business hours change is very unlikely at this point in time.
April 11, 2017 at 3:43 pm
Give this a shot...I think it satisfies all requirements. Let me know if it produces the desired results...it's hard to be certain with the limited test data. If it does, let me know if any questions on how it works because it's more important that you actually understand the code vs. just copy and paste something else.
DECLARE @t TABLE(id INT,StartDt DATETIME,EndDt DATETIME);
--I made the ID unique for each "time entry"...easier to use a unique id to refer to each time entry
INSERT INTO @t VALUES(1,'2017-04-01 18:11:06.000','2017-04-04 17:18:15.000');
INSERT INTO @t VALUES(2,'2017-04-09 15:04:47.000','2017-04-09 17:18:51.000');
INSERT INTO @t VALUES(3,'2017-04-03 18:36:57.000','2017-04-05 16:51:57.000');
INSERT INTO @t VALUES(4,'2017-04-01 16:46:33.000','2017-04-04 12:29:15.000');
INSERT INTO @t VALUES(5,'2017-04-02 20:14:22.000','2017-04-03 09:36:57.000');
--Simplifed your CTE to just hold the biz time parts...this assumes the same biz hours for every respective day of the year
--you could adjust this to account for specific days having different hours
;WITH CTE_Business_Hours AS
(
SELECT 1 [DayOfWeek],
'11:00' BusinessDayStartHour,
'19:00' BusinessDayFinishHour-- Sunday
UNION ALL
SELECT 2 [DayOfWeek],
'08:30' BusinessDayStartHour,
'21:00' BusinessDayFinishHour-- Monday
UNION ALL
SELECT 3 [DayOfWeek],
'08:30' BusinessDayStartHour,
'21:00' BusinessDayFinishHour-- Tuesday
UNION ALL
SELECT 4 [DayOfWeek],
'08:30' BusinessDayStartHour,
'21:00' BusinessDayFinishHour-- Wednesday
UNION ALL
SELECT 5 [DayOfWeek],
'08:30' BusinessDayStartHour,
'21:00' BusinessDayFinishHour-- Thursday
UNION ALL
SELECT 6 [DayOfWeek],
'08:30' BusinessDayStartHour,
'21:00' BusinessDayFinishHour-- Friday
UNION ALL
SELECT 7 [DayOfWeek],
'08:30' BusinessDayStartHour,
'21:00' BusinessDayFinishHour --- Saturday
)
--Added an additional CTE to compute the amount of Seconds in a Biz day...will come into play down below:
,FinalBusiness_Hours
as(
select
[DayOFWeek]
,BusinessDayStartHour
,BusinessDayFinishHour
,datediff(SECOND,BusinessDayStartHour,BusinessDayFinishHour) as SecondsInDay
from CTE_Business_Hours
)
--Determine how many Days there are in the time entry series
,PreQuery as
(
select *
from @t t
--Determine how many Days there are in the time entry series by using datediff on the start and end dates
cross apply(
select datediff(d,cast(t.StartDt as Date),cast(t.EndDt as date)) + 1 as [Days]
)Days
)
--Now build a tally table...inline tally table to help us split/factor out our dates
,E1(Number) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
) --10E+1 or 10 rows
,E2(Number) AS (SELECT 1 FROM E1 a, E1 b) --10E+2 or 100 rows
,E4(Number) AS (SELECT 1 FROM E2 a, E2 b) --10E+4 or 10,000 rows max
--This provides the base CTE and limits the number of rows right up front for both a performance gain and prevention of accidental overruns
,cteTally(Number) AS ( SELECT TOP (select max(Days) from PreQuery) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4 )
--We want to do a union all bc we want 0 in the count bc we want to start the counter at the time that was passed in
,FinalTally(Number) AS (
select 0 as Number
union ALL
select Number from cteTally
)
--Final Query...join our sample data to the tally table so we can produce 1 row per day in each respective time series:
,FinalQuery as
(
select *
from PreQuery pq
--Join to our numbers table to "Factor out" our time ranges into individual days: 1 row = 1 day per time range
cross apply(
select
ft.Number
from FinalTally ft
where pq.[Days] > ft.Number
)Number
--Create our column that determines the actual dates in between the StartDT and EndDT
--We need to get these as actual dates so we can apply the logic for day of week start and finish hours
cross apply(
select
Dateadd(dd,number.number,cast(Startdt as Date)) as DayRangeValue
)DayRangeValue
--Find out which day of the week the day range value fell on:
cross apply(
select datepart(dw,DayRangeValue.DayRangeValue) as DayofWeekForDayRangeValue
)DayofWeekForDayRangeValue
--Now determine how many hours should be applied for that particular day
--Just join to the FinalBusiness_hours CTE to get the values...we will worry about the logic for how to use these values down below:
outer apply(
select
fbh.SecondsInDay
,fbh.BusinessDayStartHour
,fbh.BusinessDayFinishHour
from FinalBusiness_Hours fbh
where fbh.[DayOfWeek] = DayofWeekForDayRangeValue.DayofWeekForDayRangeValue
)BusinessHours
--Now create columns for the startdt and enddt TIMES...get rid of the actual date components:
outer apply(
select
Cast(startdt as Time) as StartDtTime
,Cast(Enddt as Time) as EndDtTime
)TimeConversions
--Determine if the day is the START of a day Range or the END of a day Range
outer apply(
select
case
--If it is the last day in the range...special logic needed
when [Days] = (Number.Number +1) Then 1 else 0 End as DayRangeEnd
--If it is the first day in the range...special logic needed
,case
when Number.Number = 0 then 1 else 0 End as DayRangeStart
)DayDetails
--find the minimum end time...what came first? The end time or the business day finish hour?
outer apply(
(SELECT min(v) as MinimumDayTime
FROM (VALUES
(EnddtTime)
,(BusinessDayFinishHour)
)
AS value(v))
)MinimumDayTime
--Find the max start time...what came last...the start time or the biz day start hour?
outer apply(
(SELECT max(v) as MaximumDayTime
FROM (VALUES
(StartDtTime)
,(BusinessDayStartHour)
)
AS value(v))
)MaximumDayTime
--Now build our final case statement that uses the fields from the joins above to calculate how many seconds occured during biz hours in each day
outer apply(
select
case
--Account for starting and ending on the same day
--If the start and end happen on the same day...and
when DayRangeEnd = 1 AND DayRangeStart = 1 AND MaximumDayTime >= MinimumDayTime
Then 0
when DayRangeEnd = 1 AND DayRangeStart = 1 AND MaximumDayTime < MinimumDayTime
Then datediff(second,MaximumDayTime,MinimumDayTime)
--If it is the last day in the range...
--on the last day...if the last time is before biz start time...set value to 0
when DayRangeEnd = 1 AND EnddtTime <= BusinessDayStartHour
Then 0
--On the last day...if the end time after the biz start...let us get the datediff until either the biz end time or the actual end time
when DayRangeEnd = 1 AND EnddtTime > BusinessDayStartHour
Then datediff(second,BusinessDayStartHour,MinimumDayTime.MinimumDayTime)
--If it is the first day in the range...and there was more than 1 day in the range....
--if the start date was AFTER the bizday finish hour...set value to 0
when DayRangeStart =1 and [Days] not in (1) AND MaximumDayTime >= BusinessDayFinishHour
Then 0
--IF the start date was before the biz day finish hour...let us get a datediff compare
when DayRangeStart =1 and [Days] not in (1) AND MaximumDayTime < BusinessDayFinishHour
Then datediff(second,MaximumDayTime,BusinessDayFinishHour)
--IF none of the above condidtions...it was a full day in the range so just take the total seconds in a full day:
else SecondsInDay
end as FinalSecondsForDay
)FinalSecondsForDay
)
select
ID
,StartDt
,EndDt
--Convert back to hours
,sum(cast(FinalSecondsForDay as float))/3600 as NumberofBusinessHours
from FinalQuery
group by
ID
,StartDt
,EndDt
order by StartDt
Thanks
April 12, 2017 at 6:09 am
Thank you Grasshopper for your WONDERFUL reply.
I will do a thorough testing with the actual input of StartDt and EndDt from the actual table today (4/12/2017).
I looked at your sample code and it is wonderful. There are parts of query I understand and there are parts of query I do not understand. I'll definitely be reaching out to you once I start my testing today on the section of the query where I do not understand. Although you have commented on every section which is wonderful.
Thank you once again for your GREAT reply.
April 12, 2017 at 7:35 am
Garin T - Wednesday, April 12, 2017 6:09 AMThank you Grasshopper for your WONDERFUL reply.
I will do a thorough testing with the actual input of StartDt and EndDt from the actual table today (4/12/2017).
I looked at your sample code and it is wonderful. There are parts of query I understand and there are parts of query I do not understand. I'll definitely be reaching out to you once I start my testing today on the section of the query where I do not understand. Although you have commented on every section which is wonderful.
Thank you once again for your GREAT reply.
Hi Grasshopper,
The first question I have is; What happened when the StartDt or EndDt is outside of the business hours range?
Example: record #5 from temp table @t VALUES(5,'2017-04-02 20:14:22.000','2017-04-03 09:36:57.000');
4/2/2017 is on Sunday and the time is 20:14:22 which is outside business hours. Sunday business hours are 11:00AM - 7:00PM.
So, we should only count 4/3/2017 09:36:57 which equates to 1hr36min57sec. Right?
Thank you again for your great help.
April 12, 2017 at 7:51 am
Here's a somewhat simpler version:SELECT t.ID, t.StartDt, t.EndDt,
MinutesWorked = SUM(CASE WHEN ActualDayStart < ActualDayFinish THEN DATEDIFF(MINUTE,ActualDayStart,ActualDayFinish) ELSE 0 END)
FROM @t t
CROSS APPLY (
SELECT TOP(DATEDIFF(DAY,StartDt,EndDt)+1)
[BusinessDate] = CAST(DATEADD(DAY,ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1,CAST(StartDt AS DATE)) AS DATETIME)
FROM (VALUES (0),(0),(0),(0),(0),(0),(0)) d (n)
) x
CROSS APPLY (VALUES
(1, CAST('08:30' AS DATETIME), CAST('21:00' AS DATETIME)),
(2, '08:30', '21:00'),
(3, '08:30', '21:00'),
(4, '08:30', '21:00'),
(5, '08:30', '21:00'),
(6, '08:30', '21:00'),
(7, '11:00', '19:00') -- Sunday
) y ([DayOfWeek], BusinessDayStartHour, BusinessDayFinishHour)
CROSS APPLY (
SELECT
ActualDayStart = BusinessDate + CASE WHEN BusinessDate = CAST(StartDt AS DATE) THEN CAST(CAST(StartDt AS TIME(0)) AS DATETIME) ELSE BusinessDayStartHour END,
ActualDayFinish = BusinessDate + CASE WHEN BusinessDate = CAST(EndDt AS DATE) THEN CAST(CAST(EndDt AS TIME(0)) AS DATETIME) ELSE BusinessDayFinishHour END
) z
WHERE y.[DayOfWeek] = 1+DATEDIFF(DAY,0,BusinessDate)%7 -- settings-agnostic day of week matching
GROUP BY ID, StartDt, EndDt
ORDER BY ID, StartDt, EndDt
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 12, 2017 at 8:01 am
ChrisM@Work - Wednesday, April 12, 2017 7:51 AMHere's a somewhat simpler version:SELECT t.ID, t.StartDt, t.EndDt,
MinutesWorked = SUM(CASE WHEN ActualDayStart < ActualDayFinish THEN DATEDIFF(MINUTE,ActualDayStart,ActualDayFinish) ELSE 0 END)
FROM @t t
CROSS APPLY (
SELECT TOP(DATEDIFF(DAY,StartDt,EndDt)+1)
[BusinessDate] = CAST(DATEADD(DAY,ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1,CAST(StartDt AS DATE)) AS DATETIME)
FROM (VALUES (0),(0),(0),(0),(0),(0),(0)) d (n)
) x
CROSS APPLY (VALUES
(1, CAST('08:30' AS DATETIME), CAST('21:00' AS DATETIME)),
(2, '08:30', '21:00'),
(3, '08:30', '21:00'),
(4, '08:30', '21:00'),
(5, '08:30', '21:00'),
(6, '08:30', '21:00'),
(7, '11:00', '19:00') -- Sunday
) y ([DayOfWeek], BusinessDayStartHour, BusinessDayFinishHour)
CROSS APPLY (
SELECT
ActualDayStart = BusinessDate + CASE WHEN BusinessDate = CAST(StartDt AS DATE) THEN CAST(CAST(StartDt AS TIME(0)) AS DATETIME) ELSE BusinessDayStartHour END,
ActualDayFinish = BusinessDate + CASE WHEN BusinessDate = CAST(EndDt AS DATE) THEN CAST(CAST(EndDt AS TIME(0)) AS DATETIME) ELSE BusinessDayFinishHour END
) z
WHERE y.[DayOfWeek] = 1+DATEDIFF(DAY,0,BusinessDate)%7 -- settings-agnostic day of week matching
GROUP BY ID, StartDt, EndDt
ORDER BY ID, StartDt, EndDt
Thank you Chris M for your reply.
Obviously I am going to replace the following temp table with the actual entry of StartDt and EndDt from the actual table using CTE: In the CTE, it will have StartDt and EndDt.
Can I replace the id (1,2,3,4,5) with any unique ID from my table such LoanNumber? Or it really does not matter?
DECLARE @t TABLE(id INT,StartDt DATETIME,EndDt DATETIME);
INSERT INTO @t VALUES(1,'2017-04-01 18:11:06.000','2017-04-04 17:18:15.000');
INSERT INTO @t VALUES(2,'2017-04-09 15:04:47.000','2017-04-09 17:18:51.000');
INSERT INTO @t VALUES(3,'2017-04-03 18:36:57.000','2017-04-05 16:51:57.000');
INSERT INTO @t VALUES(4,'2017-04-01 16:46:33.000','2017-04-04 12:29:15.000');
INSERT INTO @t VALUES(5,'2017-04-02 20:14:22.000','2017-04-03 09:36:57.000');
Thank you again for your reply!
April 12, 2017 at 8:22 am
Garin T - Wednesday, April 12, 2017 8:01 AMChrisM@Work - Wednesday, April 12, 2017 7:51 AMHere's a somewhat simpler version:SELECT t.ID, t.StartDt, t.EndDt,
MinutesWorked = SUM(CASE WHEN ActualDayStart < ActualDayFinish THEN DATEDIFF(MINUTE,ActualDayStart,ActualDayFinish) ELSE 0 END)
FROM @t t
CROSS APPLY (
SELECT TOP(DATEDIFF(DAY,StartDt,EndDt)+1)
[BusinessDate] = CAST(DATEADD(DAY,ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1,CAST(StartDt AS DATE)) AS DATETIME)
FROM (VALUES (0),(0),(0),(0),(0),(0),(0)) d (n)
) x
CROSS APPLY (VALUES
(1, CAST('08:30' AS DATETIME), CAST('21:00' AS DATETIME)),
(2, '08:30', '21:00'),
(3, '08:30', '21:00'),
(4, '08:30', '21:00'),
(5, '08:30', '21:00'),
(6, '08:30', '21:00'),
(7, '11:00', '19:00') -- Sunday
) y ([DayOfWeek], BusinessDayStartHour, BusinessDayFinishHour)
CROSS APPLY (
SELECT
ActualDayStart = BusinessDate + CASE WHEN BusinessDate = CAST(StartDt AS DATE) THEN CAST(CAST(StartDt AS TIME(0)) AS DATETIME) ELSE BusinessDayStartHour END,
ActualDayFinish = BusinessDate + CASE WHEN BusinessDate = CAST(EndDt AS DATE) THEN CAST(CAST(EndDt AS TIME(0)) AS DATETIME) ELSE BusinessDayFinishHour END
) z
WHERE y.[DayOfWeek] = 1+DATEDIFF(DAY,0,BusinessDate)%7 -- settings-agnostic day of week matching
GROUP BY ID, StartDt, EndDt
ORDER BY ID, StartDt, EndDtThank you Chris M for your reply.
Obviously I am going to replace the following temp table with the actual entry of StartDt and EndDt from the actual table using CTE: In the CTE, it will have StartDt and EndDt.
Can I replace the id (1,2,3,4,5) with any unique ID from my table such LoanNumber? Or it really does not matter?
DECLARE @t TABLE(id INT,StartDt DATETIME,EndDt DATETIME);
INSERT INTO @t VALUES(1,'2017-04-01 18:11:06.000','2017-04-04 17:18:15.000');
INSERT INTO @t VALUES(2,'2017-04-09 15:04:47.000','2017-04-09 17:18:51.000');
INSERT INTO @t VALUES(3,'2017-04-03 18:36:57.000','2017-04-05 16:51:57.000');
INSERT INTO @t VALUES(4,'2017-04-01 16:46:33.000','2017-04-04 12:29:15.000');
INSERT INTO @t VALUES(5,'2017-04-02 20:14:22.000','2017-04-03 09:36:57.000');Thank you again for your reply!
Yeah I guess so, although it might be better (for you) if you post up a slice of sample data.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 12, 2017 at 8:26 am
Thank you very much once again for your reply. Greatly appreciate it!
April 12, 2017 at 8:44 am
ChrisM@Work - Wednesday, April 12, 2017 8:22 AMGarin T - Wednesday, April 12, 2017 8:01 AMChrisM@Work - Wednesday, April 12, 2017 7:51 AMHere's a somewhat simpler version:SELECT t.ID, t.StartDt, t.EndDt,
MinutesWorked = SUM(CASE WHEN ActualDayStart < ActualDayFinish THEN DATEDIFF(MINUTE,ActualDayStart,ActualDayFinish) ELSE 0 END)
FROM @t t
CROSS APPLY (
SELECT TOP(DATEDIFF(DAY,StartDt,EndDt)+1)
[BusinessDate] = CAST(DATEADD(DAY,ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1,CAST(StartDt AS DATE)) AS DATETIME)
FROM (VALUES (0),(0),(0),(0),(0),(0),(0)) d (n)
) x
CROSS APPLY (VALUES
(1, CAST('08:30' AS DATETIME), CAST('21:00' AS DATETIME)),
(2, '08:30', '21:00'),
(3, '08:30', '21:00'),
(4, '08:30', '21:00'),
(5, '08:30', '21:00'),
(6, '08:30', '21:00'),
(7, '11:00', '19:00') -- Sunday
) y ([DayOfWeek], BusinessDayStartHour, BusinessDayFinishHour)
CROSS APPLY (
SELECT
ActualDayStart = BusinessDate + CASE WHEN BusinessDate = CAST(StartDt AS DATE) THEN CAST(CAST(StartDt AS TIME(0)) AS DATETIME) ELSE BusinessDayStartHour END,
ActualDayFinish = BusinessDate + CASE WHEN BusinessDate = CAST(EndDt AS DATE) THEN CAST(CAST(EndDt AS TIME(0)) AS DATETIME) ELSE BusinessDayFinishHour END
) z
WHERE y.[DayOfWeek] = 1+DATEDIFF(DAY,0,BusinessDate)%7 -- settings-agnostic day of week matching
GROUP BY ID, StartDt, EndDt
ORDER BY ID, StartDt, EndDtThank you Chris M for your reply.
Obviously I am going to replace the following temp table with the actual entry of StartDt and EndDt from the actual table using CTE: In the CTE, it will have StartDt and EndDt.
Can I replace the id (1,2,3,4,5) with any unique ID from my table such LoanNumber? Or it really does not matter?
DECLARE @t TABLE(id INT,StartDt DATETIME,EndDt DATETIME);
INSERT INTO @t VALUES(1,'2017-04-01 18:11:06.000','2017-04-04 17:18:15.000');
INSERT INTO @t VALUES(2,'2017-04-09 15:04:47.000','2017-04-09 17:18:51.000');
INSERT INTO @t VALUES(3,'2017-04-03 18:36:57.000','2017-04-05 16:51:57.000');
INSERT INTO @t VALUES(4,'2017-04-01 16:46:33.000','2017-04-04 12:29:15.000');
INSERT INTO @t VALUES(5,'2017-04-02 20:14:22.000','2017-04-03 09:36:57.000');Thank you again for your reply!
Yeah I guess so, although it might be better (for you) if you post up a slice of sample data.
Hi Chris M,
I've attached a sample data in excel format.
What is happening is; when I run with multiple date range such as 4/1/2017 to 4/15, it is causing MSG 1014, Level 15, State 1, Line 7 (TOP clause contains an invalid value).
When I run for just one day such as 4/1/2017 - 4/1/2017, it successfully ran.
Thank you again for your help.
Below is the revised query where I replaced the reference to temp table and replaced it with CTE which contains ID (Loan Number), StartDt and EndDt.
DECLARE @BeginDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @status VARCHAR(MAX)
SET @BeginDate = '04-1-2017'
SET @EndDate = '04-15-2017'
;WITH t as
(
SELECT
id = l.LoanNumber,
StartDt = l.InitialEntryTimestamp ,
EndDt = CASE WHEN l.LoanStatus IN ('AA','APP','AP') THEN l.ApprovalDate
WHEN l.LoanStatus IN ('AD', 'DEC') THEN l.DeclinedDate
WHEN l.LoanStatus IN ('OFF') THEN l.CounterOfferDate
ELSE ResponseDate
END
FROM Loans l
WHERE
l.Source = N'GATEWAY'
AND l.InitialEntryTimestamp >= @BeginDate
AND l.InitialEntryTimestamp < Dateadd(Day,1,@EndDate)
)
SELECT t.ID, t.StartDt, t.EndDt,
MinutesWorked = SUM(CASE WHEN ActualDayStart < ActualDayFinish THEN DATEDIFF(MINUTE,ActualDayStart,ActualDayFinish) ELSE 0 END)
FROM t t
CROSS APPLY (
SELECT TOP(DATEDIFF(DAY,StartDt,EndDt)+1)
[BusinessDate] = CAST(DATEADD(DAY,ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1,CAST(StartDt AS DATE)) AS DATETIME)
FROM (VALUES (0),(0),(0),(0),(0),(0),(0)) d (n)
) x
CROSS APPLY (VALUES
(1, CAST('08:30' AS DATETIME), CAST('21:00' AS DATETIME)),
(2, '08:30', '21:00'),
(3, '08:30', '21:00'),
(4, '08:30', '21:00'),
(5, '08:30', '21:00'),
(6, '08:30', '21:00'),
(7, '11:00', '19:00') -- Sunday
) y ([DayOfWeek], BusinessDayStartHour, BusinessDayFinishHour)
CROSS APPLY (
SELECT
ActualDayStart = BusinessDate + CASE WHEN BusinessDate = CAST(StartDt AS DATE) THEN CAST(CAST(StartDt AS TIME(0)) AS DATETIME) ELSE BusinessDayStartHour END,
ActualDayFinish = BusinessDate + CASE WHEN BusinessDate = CAST(EndDt AS DATE) THEN CAST(CAST(EndDt AS TIME(0)) AS DATETIME) ELSE BusinessDayFinishHour END
) z
WHERE y.[DayOfWeek] = 1+DATEDIFF(DAY,0,BusinessDate)%7 -- settings-agnostic day of week matching
GROUP BY ID, StartDt, EndDt
ORDER BY ID, StartDt, EndDt
April 12, 2017 at 8:54 am
Firstly - can you prepare the sample data properly please? That means preparing INSERTs to populate a table, and making sure that the data provided adequately reproduces the problem domain.
Secondly, this
FROM (VALUES (0),(0),(0),(0),(0),(0),(0)) d (n)
) x
only covers seven days difference between start date and end date. You might want to calculate the max difference between start date and end date, and adjust the VALUES subquery to a more appropriate level. You might also want to ensure that start date and end date are the correct way around in your data - maybe use an additional predicate in your WHERE clause.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 12, 2017 at 9:24 am
Garin T - Wednesday, April 12, 2017 7:35 AMGarin T - Wednesday, April 12, 2017 6:09 AMThank you Grasshopper for your WONDERFUL reply.
I will do a thorough testing with the actual input of StartDt and EndDt from the actual table today (4/12/2017).
I looked at your sample code and it is wonderful. There are parts of query I understand and there are parts of query I do not understand. I'll definitely be reaching out to you once I start my testing today on the section of the query where I do not understand. Although you have commented on every section which is wonderful.
Thank you once again for your GREAT reply.Hi Grasshopper,
The first question I have is; What happened when the StartDt or EndDt is outside of the business hours range?
Example: record #5 from temp table @t VALUES(5,'2017-04-02 20:14:22.000','2017-04-03 09:36:57.000');
4/2/2017 is on Sunday and the time is 20:14:22 which is outside business hours. Sunday business hours are 11:00AM - 7:00PM.
So, we should only count 4/3/2017 09:36:57 which equates to 1hr36min57sec. Right?
Thank you again for your great help.
Let's look at example record 5...StartTime = 2017-04-02 20:14:22.000 and EndTime = 2017-04-03 09:36:57.000
The time series starts on a Sunday and ends on a Monday.
The business hours for Sunday are 11:00 to 19:00. And the business hours for Monday are 8:30 to 21:00.
In record 5...we started AFTER the business hours for Sunday so we count no time on Sunday at all. Then we should start counting from the business hour start of Monday. So we go from the Biz hour start of Monday to the End time of the time series. So that's 8:30 to 09:36:57.000. That's about an 1 hour and 6 minutes. Or 1.1158 hours as my code indicates or about 66 minutes as Chris's code indicates...both correct results.
Chris's code and mine both produce the the same results (his final result is in minutes and mine was in hours). As he noted in his previous response...his code only works if the the day difference between time entries is 7 or less days. Something that is easily configurable by changing this portion of his code.
SELECT TOP(DATEDIFF(DAY,StartDt,EndDt)+1)
[BusinessDate] = CAST(DATEADD(DAY,ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1,CAST(StartDt AS DATE)) AS DATETIME)
FROM (VALUES (0),(0),(0),(0),(0),(0),(0)) d (n)
My code should work on any date range difference because of this:
--Now build a tally table...inline tally table to help us split/factor out our dates
,E1(Number) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
) --10E+1 or 10 rows
,E2(Number) AS (SELECT 1 FROM E1 a, E1 b) --10E+2 or 100 rows
,E4(Number) AS (SELECT 1 FROM E2 a, E2 b) --10E+4 or 10,000 rows max
--This provides the base CTE and limits the number of rows right up front for both a performance gain and prevention of accidental overruns
,cteTally(Number) AS ( SELECT TOP (select max(Days) from PreQuery) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4 )
--We want to do a union all bc we want 0 in the count bc we want to start the counter at the time that was passed in
,FinalTally(Number) AS (
select 0 as Number
union ALL
select Number from cteTally
)
The key is this section of the code...it will produce an inline tally table with a number of rows equal to your time series with the greatest day day difference.
SELECT TOP (select max(Days) from PreQuery
Chris's code is definitely a more compact solution than mine but I constructed mine with CTEs so you can "follow along" to see what the code is doing. I'd reccomend running through the code and doing a select from each CTE along the way so you can see what's actually happening. That should not only make my code more clear...by Chris's solution as well.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply