Solved

Calculating Business Hours

Posted on 2016-11-19
19
65 Views
Last Modified: 2016-12-03
Hi

Using SQL 2008

I am trying to calculate the time in hours between two datetime fields.

However the query must take into account business hours

These are Mon - Fri 07:00 to 00:00 (midnight)
Saturday and Sunday and Public Holidays 08:00 to 18:00

Example

Open Date                                  Close Date
2016-11-17 23:00:00.000            2016-11-18 08:00:00.000

The result would be 2

Any help would be appreciated
0
Comment
Question by:halifaxman
  • 8
  • 7
  • 2
  • +2
19 Comments
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41894125
You can use

DATEDIFF ( hh , StartDate , EndDate  )

If you could provide some data and the expected output, we shall do it for you.
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41894182
Complete.. Please try..

Table Creation

CREATE TABLE Test
(
	 Id INT 
	,StartDate DATETIME
	,EndDate DATETIME
)
GO

INSERT INTO Test VALUES 
( 1 ,  '2016-11-17 23:00:00.000'     ,       '2016-11-18 08:00:00.000' ),
( 2 ,  '2016-11-19 08:00:00.000'     ,       '2016-11-19 12:00:00.000' )
GO

Open in new window


Query

DECLARE @StartDate AS DATETIME = '2001-01-01'
;WITH SingleDigits(Number) AS
(
    SELECT Number
    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8),
    (9), (0)) AS X(Number)
)
,Series AS
(
    SELECT (d1.Number+1) + (10*d2.Number) + (100*d3.Number) + (1000*d4.Number) Number
    from
    SingleDigits as d1,
    SingleDigits as d2,
    SingleDigits as d3,
    SingleDigits as d4
)
,CTE0 AS
(
	SELECT *, DATEADD(d,Number,@StartDate) st FROM Series 
)
,CTE1 AS
(
	SELECT Id,StartDate , EndDate , CAST(StartDate AS DATE) sdate , CAST(EndDate AS DATE) edate  
	FROM Test	
)
,CTE2 AS
(
	SELECT * , DATEADD(day, DATEDIFF(day, 0, CAST(sdate AS DATE)), '07:00:00') stt , DATEADD(day, DATEDIFF(day, 0, CAST(edate AS DATE)), '23:59:59') ett
	FROM CTE1 a
	INNER JOIN CTE0 b ON b.st BETWEEN a.sdate AND a.edate
)
,CTE3 AS
(
	SELECT * ,
				CASE WHEN CAST(StartDate AS DATE) = CAST(st AS DATE) THEN
						
							CASE WHEN StartDate > stt THEN StartDate ELSE stt END 
				ELSE
							DATEADD(day, DATEDIFF(day, -1, CAST(stt AS DATE)), '07:00:00')
								
				END Starts
			 , 
				CASE WHEN CAST(EndDate AS DATE) = CAST(st AS DATE) THEN 
					
						CASE WHEN EndDate < ett THEN EndDate ELSE ett END								
				ELSE
						DATEADD(day, DATEDIFF(day, 0, CAST(stt AS DATE)), '23:59:59')
				END Ends	
	FROM CTE2
)
SELECT Id,SUM(hrx) Hours FROM 
(
	SELECT * , ROUND( DATEDIFF( MINUTE , Starts , Ends ) * 1. / 60 , 1 ) hrx FROM CTE3
)k GROUP BY Id

Open in new window


Output

Id          Hours
----------- ---------------------------------------
1           2.000000
2           4.000000

(2 row(s) affected)

Open in new window


Hope it helps !!
0
 

Author Comment

by:halifaxman
ID: 41894685
Thanks will give it a go today
0
 

Author Comment

by:halifaxman
ID: 41894818
Hi

Ran this on my data - it part works

 The query does not take into account Saturdays and Sundays - 08:00 to 18:00 - they are present calculating at weekday opening hours i.e.
17 hours

Also  Bank Holidays such as Christmas Day, Good Friday, Easter Monday need to be taken into account they will be same hrs as Saturday and Sunday 08:00 to 18:00 hrs

The query runs slow at the moment - 15 minutes at present

Thanks for your help on this
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41895366
Saturday , Sunday & Bank Holidays such as Christmas Day, Good Friday, Easter Monday need to be taken

Handled both. For holidays I have created a new Holiday table and inserted 3 rows. you can insert all your data and run the query and check the output.

CREATE TABLE Holiday
(
	 Dt DATE
	,Holiday VARCHAR(100)
)
GO

INSERT INTO Holiday VALUES
('2016/10/02', 'Ghandhi Jayanti'),
('2016/08/15', 'Independence Day'),
('2016/01/26', 'Republic Day')
GO

DECLARE @StartDate AS DATETIME = '2001-01-01'
;WITH SingleDigits(Number) AS
(
    SELECT Number
    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8),
    (9), (0)) AS X(Number)
)
,Series AS
(
    SELECT (d1.Number+1) + (10*d2.Number) + (100*d3.Number) + (1000*d4.Number) Number
    from
    SingleDigits as d1,
    SingleDigits as d2,
    SingleDigits as d3,
    SingleDigits as d4
)
,CTE0 AS
(
	SELECT *, DATEADD(d,Number,@StartDate) st FROM Series 
)
,CTE1 AS
(
	SELECT Id,StartDate , EndDate , CAST(StartDate AS DATE) sdate , CAST(EndDate AS DATE) edate  
	FROM Test	
)
,CTE2 AS
(
	SELECT * , DATEADD(day, DATEDIFF(day, 0, CAST(sdate AS DATE)), '07:00:00') stt , DATEADD(day, DATEDIFF(day, 0, CAST(edate AS DATE)), '23:59:59') ett
	FROM CTE1 a
	INNER JOIN CTE0 b ON b.st BETWEEN a.sdate AND a.edate
)
,CTE3 AS
(
	SELECT * ,
				CASE WHEN CAST(StartDate AS DATE) = CAST(st AS DATE) THEN
						
							CASE WHEN StartDate > stt THEN StartDate ELSE stt END 
				ELSE
							DATEADD(day, DATEDIFF(day, -1, CAST(stt AS DATE)), '07:00:00')
								
				END Starts
			 , 
				CASE WHEN CAST(EndDate AS DATE) = CAST(st AS DATE) THEN 
					
						CASE WHEN EndDate < ett THEN EndDate ELSE ett END								
				ELSE
						DATEADD(day, DATEDIFF(day, 0, CAST(stt AS DATE)), '23:59:59')
				END Ends	
	FROM CTE2
)
SELECT Id,SUM(hrx) Hours  FROM 
(
	SELECT * , ROUND( DATEDIFF( MINUTE , Starts , Ends ) * 1. / 60 , 1 ) hrx  , DATEPART(dw,Starts) a,DATEPART(dw,Ends) b 
	FROM CTE3 
	WHERE 
		    DATEPART(dw,Starts) NOT IN (7,8) 
		AND DATEPART(dw,Ends) NOT IN (7,8)	
		AND Starts NOT IN (SELECT Dt FROM Holiday)
		AND Ends NOT IN (SELECT Dt FROM Holiday)
)k GROUP BY Id

Open in new window


The query runs slow at the moment - 15 minutes at present

Once the code is finalized then we shall check for query performance.
0
 

Author Comment

by:halifaxman
ID: 41895485
Brilliant thanks for your help with this - will try this out today
0
 

Author Comment

by:halifaxman
ID: 41895532
Hi

This is not working properly

Couple of examples

ID                     Logged                                         Completed                                  Hours
I1605-4263      2016-05-10 19:25:00.000            2016-05-17 13:00:00.000            4.600000
I1606-5092      2016-06-10 16:43:00.000            2016-06-13 10:00:00.000            7.300000


I1605-4263 should be
10th May - 4.6 hrs
11th May - 17 hrs
12th May - 17 hrs
13th May - 17 hrs
14th May - 10 hrs (Saturday)
15th May - 10 hrs (Sunday)
16th May - 17 hrs
17th May - 6 hrs

Total = 98.6 hrs


I1606-5092 should be
10th June - 7.3 hrs
11th June - 10 hrs (Saturday)
12th June - 10 hrs (Sunday)
13th June - 3hrs

Total 30.3 hrs

Thanks
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41895538
Do you want to include Sat/Sunday?
0
 

Author Comment

by:halifaxman
ID: 41895554
Hi

Yes

Saturday and Sunday and Public Holidays the hours are different from weekdays - Sat/Sun/Public Holidays are 08:00 to 18:00 (10 hrs)

Thanks
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41895562
Ok. try..

CREATE TABLE Holiday
(
	 Dt DATE
	,Holiday VARCHAR(100)
)
GO

INSERT INTO Holiday VALUES
('2016/10/02', 'Ghandhi Jayanti'),
('2016/08/15', 'Independence Day'),
('2016/01/26', 'Republic Day')
GO

DECLARE @StartDate AS DATETIME = '2001-01-01'
;WITH SingleDigits(Number) AS
(
    SELECT Number
    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8),
    (9), (0)) AS X(Number)
)
,Series AS
(
    SELECT (d1.Number+1) + (10*d2.Number) + (100*d3.Number) + (1000*d4.Number) Number
    from
    SingleDigits as d1,
    SingleDigits as d2,
    SingleDigits as d3,
    SingleDigits as d4
)
,CTE0 AS
(
	SELECT *, DATEADD(d,Number,@StartDate) st FROM Series 
)
,CTE1 AS
(
	SELECT Id,StartDate , EndDate , CAST(StartDate AS DATE) sdate , CAST(EndDate AS DATE) edate  
	FROM Test	
)
,CTE2 AS
(
	SELECT * , DATEADD(day, DATEDIFF(day, 0, CAST(sdate AS DATE)), '07:00:00') stt 
	, DATEADD(day, DATEDIFF(day, 0, CAST(edate AS DATE)), '23:59:59') ett
	FROM CTE1 a
	INNER JOIN CTE0 b ON b.st BETWEEN a.sdate AND a.edate
)
,CTE3 AS
(
	SELECT * ,DATEPART(dw,StartDate) d1,
				CASE WHEN CAST(StartDate AS DATE) = CAST(st AS DATE) THEN
						
							CASE WHEN StartDate > stt THEN StartDate ELSE stt END 
				ELSE
							CASE WHEN DATEPART(dw,StartDate) IN (7,8) THEN
								DATEADD(day, DATEDIFF(day, -1, CAST(stt AS DATE)), '08:00:00')
							ELSE
								DATEADD(day, DATEDIFF(day, -1, CAST(stt AS DATE)), '07:00:00')
							END
								
				END Starts
			 , 
				CASE WHEN CAST(EndDate AS DATE) = CAST(st AS DATE) THEN 
					
						CASE WHEN EndDate < ett THEN EndDate ELSE ett END								
				ELSE
						CASE WHEN DATEPART(dw,EndDate) IN (7,8) THEN
							DATEADD(day, DATEDIFF(day, 0, CAST(stt AS DATE)), '18:00:00')
						ELSE
							DATEADD(day, DATEDIFF(day, 0, CAST(stt AS DATE)), '23:59:59')
						END
				END Ends	
	FROM CTE2
)
SELECT Id,SUM(hrx) Hours  FROM 
(
	SELECT * , ROUND( DATEDIFF( MINUTE , Starts , Ends ) * 1. / 60 , 1 ) hrx
	FROM CTE3 	
)k GROUP BY Id

Open in new window

0
 

Author Comment

by:halifaxman
ID: 41895749
Hi

This is still counting Saturday and Sunday as 17 instead of 10 hrs

Example

ID              Logged                                               Completed                              Hours
I1601-7725      2016-01-27 14:53:00.000      2016-02-01 22:02:00.000      92.100000

This should be 77.1 hrs

Thanks
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41895771
Pls try..

CREATE TABLE Holiday
(
	 Dt DATE
	,Holiday VARCHAR(100)
)
GO

INSERT INTO Holiday VALUES
('2016/10/02', 'Ghandhi Jayanti'),
('2016/08/15', 'Independence Day'),
('2016/01/26', 'Republic Day')
GO

DECLARE @StartDate AS DATETIME = '2001-01-01'
;WITH SingleDigits(Number) AS
(
    SELECT Number
    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8),
    (9), (0)) AS X(Number)
)
,Series AS
(
    SELECT (d1.Number+1) + (10*d2.Number) + (100*d3.Number) + (1000*d4.Number) Number
    from
    SingleDigits as d1,
    SingleDigits as d2,
    SingleDigits as d3,
    SingleDigits as d4
)
,CTE0 AS
(
	SELECT *, DATEADD(d,Number,@StartDate) st FROM Series 
)
,CTE1 AS
(
	SELECT Id,StartDate , EndDate , CAST(StartDate AS DATE) sdate , CAST(EndDate AS DATE) edate  
	FROM Test	
)
,CTE2 AS
(
	SELECT * , DATEADD(day, DATEDIFF(day, 0, CAST(sdate AS DATE)), '07:00:00') stt 
	, DATEADD(day, DATEDIFF(day, 0, CAST(edate AS DATE)), '23:59:59') ett
	FROM CTE1 a
	INNER JOIN CTE0 b ON b.st BETWEEN a.sdate AND a.edate
)
,CTE3 AS
(
	SELECT * ,DATEPART(dw,StartDate) d1,
				CASE WHEN CAST(StartDate AS DATE) = CAST(st AS DATE) THEN
						
							CASE WHEN StartDate > stt THEN 
							
								CASE WHEN DATEPART(dw,StartDate) IN (7,8) THEN 
									DATEADD(day, DATEDIFF(day, -1, CAST(StartDate AS DATE)), '08:00:00')
								ELSE
									DATEADD(day, DATEDIFF(day, -1, CAST(StartDate AS DATE)), '07:00:00')
								END 
							
							
							ELSE 
							
								CASE WHEN DATEPART(dw,stt) IN (7,8) THEN 
									DATEADD(day, DATEDIFF(day, -1, CAST(stt AS DATE)), '08:00:00')
								ELSE
									DATEADD(day, DATEDIFF(day, -1, CAST(stt AS DATE)), '07:00:00')
								END  
							
							
							
							END 
				ELSE
							CASE WHEN DATEPART(dw,StartDate) IN (7,8) THEN
								DATEADD(day, DATEDIFF(day, -1, CAST(stt AS DATE)), '08:00:00')
							ELSE
								DATEADD(day, DATEDIFF(day, -1, CAST(stt AS DATE)), '07:00:00')
							END
								
				END Starts
			 , 
				CASE WHEN CAST(EndDate AS DATE) = CAST(st AS DATE) THEN 
					
						CASE WHEN EndDate < ett THEN  

									CASE WHEN DATEPART(dw,EndDate) IN (7,8) THEN
										DATEADD(day, DATEDIFF(day, 0, CAST(EndDate AS DATE)), '18:00:00')
									ELSE
										DATEADD(day, DATEDIFF(day, 0, CAST(EndDate AS DATE)), '23:59:59')
									END						
						
						ELSE  
						
									CASE WHEN DATEPART(dw,ett) IN (7,8) THEN
										DATEADD(day, DATEDIFF(day, 0, CAST(ett AS DATE)), '18:00:00')
									ELSE
										DATEADD(day, DATEDIFF(day, 0, CAST(ett AS DATE)), '23:59:59')
									END
						
						END								
				ELSE
						CASE WHEN DATEPART(dw,EndDate) IN (7,8) THEN
							DATEADD(day, DATEDIFF(day, 0, CAST(stt AS DATE)), '18:00:00')
						ELSE
							DATEADD(day, DATEDIFF(day, 0, CAST(stt AS DATE)), '23:59:59')
						END
				END Ends	
	FROM CTE2
)
SELECT Id,SUM(hrx) Hours  FROM 
(
	SELECT * , ROUND( DATEDIFF( MINUTE , Starts , Ends ) * 1. / 60 , 1 ) hrx
	FROM CTE3 	
)k GROUP BY Id

Open in new window

0
 

Author Comment

by:halifaxman
ID: 41895829
Hi

It seems to be treating every day as Saturday and Sunday
also if the Logged and Completed days are on the same day its giving it a value of -7

Example

ID                     Logged                                          Completed                                    Hours
I1609-1396      2016-09-05 07:56:00.000            2016-09-05 08:25:00.000            -7.000000
I1609-2078      2016-09-05 14:52:00.000            2016-09-06 15:17:00.000            10.000000

1396 should be 0.5 hrs
2078 should be 17 hrs

Thanks
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41896061
Could you please post list of rows and the expected data?
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 41896734
I think the query below will do it.  I used an inline tally table to cover multiple days.  I only used a max of 10 days but it's easy enough to extend the tally table to 100 or even 1000 rows.  I used CROSS APPLYs to do the preliminary calcs to make the main SELECT code "less cluttered".

--"Holidays" structure, but no data; naturally use your real holiday table or load dates to the one below.
--!!!!!Make sure you don't run the next line if your real holidays table is named "dbo.Holidays"!!!!
IF OBJECT_ID('dbo.Holidays') IS NOT NULL DROP TABLE dbo.Holidays;
CREATE TABLE dbo.Holidays ( date date PRIMARY KEY );

--"Test" structure and data.
IF OBJECT_ID('dbo.Test') IS NOT NULL DROP TABLE dbo.Test;
CREATE TABLE dbo.Test ( Id varchar(20), Logged datetime, Completed datetime );
INSERT INTO dbo.Test VALUES
( 'I1605-4263', '2016-05-10 19:25:00.000', '2016-05-17 13:00:00.000' ),
( 'I1606-5092', '2016-06-10 16:43:00.000', '2016-06-13 10:00:00.000' );

--actual query to calc hours
;WITH
cteTally10 AS (
    SELECT * FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS numbers(number)
)
SELECT
    ID,
    /* showing each day just for reference: you could instead sum up hours for all dates */
    work_date,
    CASE WHEN work_start_time > last_work_hour THEN 0.0
         ELSE ROUND(DATEDIFF(MINUTE,
             CASE WHEN work_start_time < first_work_hour THEN first_work_hour ELSE work_start_time END,
             CASE WHEN work_end_time > last_work_hour THEN last_work_hour ELSE work_end_time END) / 60.0, 1)
         END AS hours_worked,
     /* just for reference */
    logged, completed
FROM dbo.Test t
INNER JOIN cteTally10 days ON days.number BETWEEN 0 AND DATEDIFF(DAY, t.Logged, t.Completed)
CROSS APPLY (
    SELECT CAST(DATEADD(DAY, days.number, t.Logged) AS date) AS work_date
) AS assign_alias_names_1
CROSS APPLY (
    SELECT CAST(CASE WHEN days.number = 0 /*first day*/ THEN t.Logged - work_date
            ELSE '00:00' END AS smalldatetime) AS work_start_time,
        CAST(CASE WHEN days.number = DATEDIFF(DAY, t.Logged, t.Completed) /*last day*/ THEN t.Completed - work_date
            ELSE '19000102' END AS smalldatetime) AS work_end_time
) AS assign_alias_names_2
CROSS APPLY (
    SELECT CAST(CASE
        WHEN DATEDIFF(DAY, 0, work_date) % 7 IN (5, 6) /*Saturday,Sunday*/
        THEN 1
        WHEN EXISTS(SELECT 1 FROM Holidays h WHERE h.date = work_date)
        THEN 1
        ELSE 0 END AS bit) AS is_weekend_or_holiday
) AS assign_alias_names_3
CROSS APPLY (
    SELECT CAST(CASE WHEN is_weekend_or_holiday = 0 THEN '07:00' ELSE '08:00' END AS smalldatetime) AS first_work_hour,
        CAST(CASE WHEN is_weekend_or_holiday = 0 THEN '19000102' ELSE '18:00' END AS smalldatetime) AS last_work_hour
) AS assign_alias_names_4
ORDER BY ID, work_date
0
 
LVL 15

Expert Comment

by:mcmonap
ID: 41896811
My effort, calculating hours generating a cte that has all the possible work days and hours in it.  I think the queries at the end could probably  take some optimisation and I made an issue with having the end times as 23:59 which is why I am converting between minutes and hours and adding 1s <sigh>

Table build and population:
CREATE DATABASE Test18

USE Test18;

CREATE TABLE workHours(
	ID INT IDENTITY(1,1)
	, [Day] INT
	, startHour TIME
	, endHour TIME)

CREATE TABLE holiday(
	ID INT IDENTITY(1,1)
	, [Date] DATE
	, startHour TIME
	, endHour TIME)

CREATE TABLE [example](
	exampleID INT IDENTITY(1,1)
	, startHour datetime
	, endHour datetime)
GO

INSERT INTO workHours VALUES (1, '08:00', '18:00')
INSERT INTO workHours VALUES (2, '07:00', '23:59')
INSERT INTO workHours VALUES (3, '07:00', '23:59')
INSERT INTO workHours VALUES (4, '07:00', '23:59')
INSERT INTO workHours VALUES (5, '07:00', '23:59')
INSERT INTO workHours VALUES (6, '07:00', '23:59')
INSERT INTO workHours VALUES (7, '08:00', '18:00')

INSERT INTO holiday VALUES('2016-12-25', '08:00', '18:00')
INSERT INTO holiday VALUES('2016-12-26', '08:00', '18:00')
INSERT INTO holiday VALUES('2017-01-01', '08:00', '18:00')

INSERT INTO example VALUES('2016-11-17 23:00:00.000','2016-11-18 08:00:00.000')
INSERT INTO example VALUES('2016-11-18 08:00:00.000','2016-11-18 11:00:00.000')
INSERT INTO example VALUES('2016-11-19 23:00:00.000','2016-11-23 14:00:00.000')
INSERT INTO example VALUES('2016-11-27 15:00:00.000','2016-12-14 19:00:00.000')
INSERT INTO example VALUES('2016-12-13 08:00:00.000','2016-12-18 12:00:00.000')
INSERT INTO example VALUES('2016-12-22 11:00:00.000','2016-12-26 22:00:00.000')
INSERT INTO example VALUES('2016-12-29 18:00:00.000','2017-01-03 17:00:00.000')

Open in new window

Query to run:
USE TEST18;

DECLARE @currentDate DATE
SELECT @currentDate=MIN(startHour) FROM example

DECLARE @cteWorkingTimes TABLE (
	[Date] DATE
	, startHour TIME
	, endHour TIME
	, WorkingHours INT)
--Build list of all possible dates and working hours based on the example table
WHILE (SELECT MAX(endHour) FROM example) > @currentDate
BEGIN
	INSERT INTO @cteWorkingTimes ([Date], startHour, endHour, WorkingHours)
	SELECT
		@CurrentDate
		, startHour
		, endHour
		, CASE DATEPART(N,endHour)
			WHEN 59 THEN DATEDIFF(N,startHour,endHour)+1
			ELSE DATEDIFF(N,startHour,endHour)
		END WorkingHours
	FROM
		[dbo].[workHours]
	WHERE
		DATEPART(weekday,@currentDate) = [Day]
	
	SELECT @currentDate = DATEADD(D,1,@currentDate)
END
--Update list of all posisble dates with holiday working
UPDATE c
SET
	startHour=h.startHour
	, endHour=h.endHour
	, WorkingHours=DATEDIFF(N,h.startHour,h.endHour)
FROM
	@cteWorkingTimes c
	JOIN holiday h ON c.[Date]=h.[date]

SELECT
	e.exampleID
	, e.startHour
	, e.endHour
	, SUM(
		CASE
			WHEN CONVERT(DATE,e.startHour)=wt.[date] AND DATEPART(N,wt.endHour)=59 THEN DATEDIFF(N,CONVERT(TIME,e.startHour),wt.endHour)+1
			WHEN CONVERT(DATE,e.startHour)=wt.[date] THEN DATEDIFF(N,CONVERT(TIME,e.startHour),wt.endHour)
			WHEN CONVERT(DATE,e.endHour)=wt.[date] THEN DATEDIFF(N,wt.startHour,CONVERT(TIME,e.endHour))
			ELSE wt.workingHours
		END/60) [workingHours]
FROM
	@cteWorkingTimes wt
	JOIN example e ON wt.[date]>=CONVERT(DATE,e.startHour) AND wt.[date]<=CONVERT(DATE,e.endHour)
GROUP BY
	e.exampleID
	, e.startHour
	, e.endHour

Open in new window

0
 

Author Comment

by:halifaxman
ID: 41897143
Scott

Just had a look at the query posted and it works nicely - thanks

With regards to

 I only used a max of 10 days but it's easy enough to extend the tally table to 100 or even 1000 rows.

I probably need to extend it to 500 rows - do I need to just keep adding (10),(11),(12) etc

Thanks
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 41897585
do I need to just keep adding (10),(11),(12) etc

Nah.  Sorry, earlier I was busy and I took a short-cut on the earlier tally table code.  Numbering all rows ourselves is too inefficient for large numbers of rows.

Instead, let's just add another "standard" CROSS JOIN (10 rows * 10 rows * 10 rows = 1000 rows) and ROW_NUMBER() (for efficiency), which will give you up to 999 days.  Btw, this method can be efficiently extended to 1M rows if needed.

;WITH
cteTally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cteTally1000 AS (
    SELECT ROW_NUMBER() OVER(ORDER BY c1.number) - 1 AS number
    FROM cteTally10 c1
    CROSS JOIN cteTally10 c2
    CROSS JOIN cteTally10 c3
)
....
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 41911426
On the outside chance that you could be using PHP, you might be interested in Practical Application #9 in this article.
https://www.experts-exchange.com/articles/20920/Handling-Time-and-Date-in-PHP-and-MySQL-OOP-Version.html
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now