Calculating Hours with Overlapping Times

[Phil Sandler]

All,

I have a table with start and end dates/times in it, and would like to
be able to calculate the number of hours represented, accounting for
overlapping records.

Note that I am looking for an answer on HOW to do this--I don't
necessarily need it to be written for me (although it would not go
unappreciated!) .
CREATE TABLE [dbo].[session_temp] (
[session_pk] [int] IDENTITY (1, 1) NOT NULL ,
[date_start] [smalldatetime] NULL ,
[date_end] [smalldatetime] NULL
) ON [PRIMARY]
GO

--These values make a very simplistic example,
--as they only represent one
--session,so using min and max would work in this case,
--but would not work if there
--were multiple sessions involved.

--hopefully you get the idea of what I am going for:

INSERT INTO session_temp
VALUES('4/4/04 9 pm','4/4/04 10pm')

INSERT INTO session_temp
VALUES('4/4/04 9 pm','4/4/04 10:30pm')

INSERT INTO session_temp
VALUES('4/4/04 9 pm','4/4/04 10:45pm')

INSERT INTO session_temp
VALUES('4/4/04 9 pm','4/4/04 11pm')

INSERT INTO session_temp
VALUES('4/4/04 9 pm','4/5/04 2am')

--the query I am looking to write would return "5"
--the one below obviously does not do what I am looking for
SELECT
SUM(CAST(DATEDI FF(ss,date_star t,date_end) AS NUMERIC(8,2))/3600)
FROM
session_temp

Thanks very much for any insight.

Phil

---
Check out my poker-only weblog at:
http://www.livejournal.com/users/chicago_phil/
Download my session-tracking spreadsheet at:
http://www.geocities.com/fibby70/

[-P-]

"Phil Sandler" <ps********@hot mail.com> wrote in message news:3c******** *************** ***@posting.goo gle.com...

All,
<snip>

Thanks very much for any insight.

Phil

How about this?

Select
date_start,
max( cast( datediff(ss, date_start, date_end ) as numeric(8,2))/3600 )
from
session_temp
group by date_start ;

--
Paul Horan[TeamSybase]

[John Gilson]

"Phil Sandler" <ps********@hot mail.com> wrote in message
news:3c******** *************** ***@posting.goo gle.com...

All,

I have a table with start and end dates/times in it, and would like to
be able to calculate the number of hours represented, accounting for
overlapping records.

Note that I am looking for an answer on HOW to do this--I don't
necessarily need it to be written for me (although it would not go
unappreciated!) .
CREATE TABLE [dbo].[session_temp] (
[session_pk] [int] IDENTITY (1, 1) NOT NULL ,
[date_start] [smalldatetime] NULL ,
[date_end] [smalldatetime] NULL
) ON [PRIMARY]
GO

--These values make a very simplistic example,
--as they only represent one
--session,so using min and max would work in this case,
--but would not work if there
--were multiple sessions involved.

--hopefully you get the idea of what I am going for:

INSERT INTO session_temp
VALUES('4/4/04 9 pm','4/4/04 10pm')

INSERT INTO session_temp
VALUES('4/4/04 9 pm','4/4/04 10:30pm')

INSERT INTO session_temp
VALUES('4/4/04 9 pm','4/4/04 10:45pm')

INSERT INTO session_temp
VALUES('4/4/04 9 pm','4/4/04 11pm')

INSERT INTO session_temp
VALUES('4/4/04 9 pm','4/5/04 2am')

--the query I am looking to write would return "5"
--the one below obviously does not do what I am looking for
SELECT
SUM(CAST(DATEDI FF(ss,date_star t,date_end) AS NUMERIC(8,2))/3600)
FROM
session_temp

Thanks very much for any insight.

Phil

---
Check out my poker-only weblog at:
http://www.livejournal.com/users/chicago_phil/
Download my session-tracking spreadsheet at:
http://www.geocities.com/fibby70/

I assume that in addition to overlapping periods there might also be
gaps, for example, a period from 9AM to 10AM and then from 11AM
to 12PM should yield 2 hours with a 1 hour gap between 10AM and 11AM.

An approach would be to find the time from the earliest start datetime
to the latest end datetime and then subtract all gap lengths from this
amount.

CREATE TABLE Periods
(
start_period SMALLDATETIME NOT NULL,
end_period SMALLDATETIME NOT NULL,
CHECK (start_period < end_period),
PRIMARY KEY (start_period, end_period)
)

CREATE VIEW PeriodGaps (start_period, end_period)
AS
SELECT MAX(P1.end_peri od), P2.start_period
FROM Periods AS P1
INNER JOIN
Periods AS P2
ON P1.start_period < P2.start_period
GROUP BY P2.start_period
HAVING MAX(P1.end_peri od) < P2.start_period

-- Sample data
INSERT INTO Periods (start_period, end_period)
VALUES ('9AM', '10:30AM')
INSERT INTO Periods (start_period, end_period)
VALUES ('9:30AM', '11:30AM')
INSERT INTO Periods (start_period, end_period)
VALUES ('1:30PM', '3:00PM')
INSERT INTO Periods (start_period, end_period)
VALUES ('3:30PM', '5:00PM')

-- Note that SMALLDATETIME values are to the minute
SELECT ((SELECT COALESCE(DATEDI FF(MINUTE,
MIN(start_perio d),
MAX(end_period) ), 0)
FROM Periods) -
(SELECT COALESCE(SUM(DA TEDIFF(MINUTE,
start_period,
end_period)), 0)
FROM PeriodGaps)) / 60.0 AS duration_in_hrs

duration_in_hrs
5.500000

--
JAG

[Joe Melville]

ps********@hotm ail.com (Phil Sandler) wrote in message news:<3c******* *************** ****@posting.go ogle.com>...

All,

I have a table with start and end dates/times in it, and would like to
be able to calculate the number of hours represented, accounting for
overlapping records.

Note that I am looking for an answer on HOW to do this--I don't
necessarily need it to be written for me (although it would not go
unappreciated!) .
CREATE TABLE [dbo].[session_temp] (
[session_pk] [int] IDENTITY (1, 1) NOT NULL ,
[date_start] [smalldatetime] NULL ,
[date_end] [smalldatetime] NULL
) ON [PRIMARY]
GO

--These values make a very simplistic example,
--as they only represent one
--session,so using min and max would work in this case,
--but would not work if there
--were multiple sessions involved.

--hopefully you get the idea of what I am going for:

INSERT INTO session_temp
VALUES('4/4/04 9 pm','4/4/04 10pm')

INSERT INTO session_temp
VALUES('4/4/04 9 pm','4/4/04 10:30pm')

INSERT INTO session_temp
VALUES('4/4/04 9 pm','4/4/04 10:45pm')

INSERT INTO session_temp
VALUES('4/4/04 9 pm','4/4/04 11pm')

INSERT INTO session_temp
VALUES('4/4/04 9 pm','4/5/04 2am')

--the query I am looking to write would return "5"
--the one below obviously does not do what I am looking for
SELECT
SUM(CAST(DATEDI FF(ss,date_star t,date_end) AS NUMERIC(8,2))/3600)
FROM
session_temp

Thanks very much for any insight.

Phil

---
Check out my poker-only weblog at:
http://www.livejournal.com/users/chicago_phil/
Download my session-tracking spreadsheet at:
http://www.geocities.com/fibby70/

Here's a stored procedure that I have created to do what you need:
/* This stored procedure combines any overlapping time segments into
a single segment in the temporary table #CombinedTime. Only non-
overlapping segements of time exist in this table. It then returns the
total hours for all these remaining segments.

Assumptions:
1. Date_Start <= Date_End
2. Date_Start, and Date_End are not null

Usage:
DECLARE @Total_Hours AS INT

EXEC @Total_Hours = CombinedTime

PRINT @Total_Hours
*/
CREATE PROCEDURE CombinedTime AS

DECLARE
@Date_Start AS SMALLDATETIME,
@Date_End AS SMALLDATETIME,
@Total_Hours AS INT

/* Create temporary table to contain combined information.
*/
CREATE TABLE #CombinedTime (
Date_Start SMALLDATETIME NOT NULL ,
Date_End SMALLDATETIME NOT NULL
)
/* Use a cursor to get every record from Session_Temp */
DECLARE Time_Cursor CURSOR FOR
SELECT Date_Start, Date_End
FROM Session_Temp
ORDER BY Date_Start ASC

OPEN Time_Cursor
FETCH NEXT FROM Time_Cursor INTO @Date_Start, @Date_End
WHILE (@@FETCH_STATUS != -1)
BEGIN
/* Update an existing record in the temporary table if Date_Start is
within the bounds of an existing record, and the Date_End is outside */
UPDATE #CombinedTime
SET Date_End = @Date_End
WHERE @Date_Start BETWEEN Date_Start AND Date_End
AND @Date_End > Date_End

/* Insert a record that falls completely outside previous ranges */
INSERT INTO #CombinedTime
(Date_Start, Date_End)
SELECT @Date_Start, @Date_End
WHERE NOT EXISTS (SELECT 1
FROM #CombinedTime
WHERE @Date_Start < Date_End)
FETCH NEXT FROM Time_Cursor INTO @Date_Start, @Date_End
END /* WHILE */

CLOSE Time_Cursor
DEALLOCATE Time_Cursor

/* Get the total hours from the non-overlapping segments */
SELECT @Total_Hours = SUM(DATEDIFF(HH , Date_Start, Date_End))
FROM #CombinedTime

DROP TABLE #CombinedTime

RETURN @Total_Hours
GO