# Thread: How to exact a span of time from a record?

1. Registered User
Join Date
Aug 2012
Posts
6

## Unanswered: How to exact a span of time from a record?

I am not sure how to go about doing this. I have a record that has a start time of 1 am and a stop time of 9pm (same day for simplicity) and I want to know how many hours during a peek time and how many were not.

for example, the application starts and inserts into the data base the start time of 1am and then the user stops the app at 9pm. Lets say the peek hours are 1pm to 7pm. I know i can do a date diff function to get how long the app ran for but how can I get the amount of hours it ran during the peek time? I know there has to be some mathematical solution to this but it is escaping me at the moment. I want to do this over many records so a a cte or pivot table is the end solution for performance.

Any help would be greatly appreciated.
Stephen

2. Registered User
Join Date
Oct 2009
Location
221B Baker St.
Posts
486
If the app was started at 1am and stopped at 9pm, it would have run all of the hours between 1pm and 7pm - would it not?

Surely, i misunderstand something.

3. Registered User
Join Date
Aug 2012
Posts
6
Correct, it would, but the point I am trying to show is that I want to able to get the amount it ran during each peek and non peek hrs. if it span 2 days or 3 hrs.

Say it starts on Monday 6pm and stops on that night at 10pm. That would be an hour of peek time and 3 hrs of off peek time.

My real problem is trying to query the records that span multiple days. If i put in the where clause to only return the records that have a start and stop day during peek hours will not get all of the records that ran multiple days.

What I am trying to figure out is how to a record that ran for three days and based on the time it started determine the amount of peek and non peek hrs it ran.

4. Registered User
Join Date
Aug 2012
Posts
6
here is an example,

Start-------------------- Stop-------------------- Hours Days
2010-11-08 10:47:20.117 2010-11-10 07:27:30.273 45 2
if the app was running for 45 hrs how can determine how many hrs were peek hrs and how many hrs were non peek

5. Registered User
Join Date
Aug 2012
Posts
45
Stephen, you don't give any clues to your data structure, so I am grabbing at straws here, but basically the math is simple: TotalTime - PeakTime = OffPeakTime. Those are really the only three elements you need to worry about. Take a look at the below code, and it might give you some ideas. I started working on it before your second post indicating a the duration could span multiple days, but the logic should be roughly the same, you will only need to add days to the equation.

Code:
```DECLARE @AppStart	DATETIME,
@AppStop	DATETIME,
@PeakStart	TIME,
@PeakStop	TIME

SELECT @AppStart = '2012-08-23 01:30:30',
@AppStop  = '2012-08-23 21:00:00',
@PeakStart = '13:00:00',
@PeakStop = '19:30:29';

WITH Times AS
(
SELECT
'TotalTime(H)' = DATEDIFF(s,@AppStart,@AppStop)/3600,
'TotalTime(M)' = (DATEDIFF(s,@AppStart,@AppStop) % 3600)/60,
'TotalTime(S)' = (DATEDIFF(s,@AppStart,@AppStop) % 3600)%60,
'PeakTime(H)' = DATEDIFF(s,@PeakStart,@PeakStop)/3600,
'PeakTime(M)' = (DATEDIFF(s,@PeakStart,@PeakStop) % 3600)/60,
'PeakTIme(S)' = (DATEDIFF(s,@PeakStart,@PeakStop) % 3600)%60
)

SELECT
[PeakTime(M)],CAST(DATEADD(HH,[TotalTime(H)] - [PeakTime(H)],0) AS TIME)) AS TIME)) AS TIME)
FROM Times```
Greg

6. Registered User
Join Date
Aug 2012
Posts
6
Thanks greg I will give it a try

7. Registered User
Join Date
Jan 2003
Location
Massachusetts
Posts
5,842
Code:
```create table test1
(starttime datetime,
endtime datetime)

insert into test1 values
('2012-08-22 01:00', '2012-08-22 21:00'),
('2012-08-22 01:00', '2012-08-22 15:00'),
('2012-08-22 14:00', '2012-08-22 21:00'),
('2012-08-22 14:00', '2012-08-22 15:00')

declare @peakstart datetime, @peakend datetime
set @peakstart = '2012-08-22 13:00'
set @peakend = '2012-08-22 19:00'

select case when starttime <= @peakstart and endtime >= @peakend then 8
when starttime <= @peakstart and endtime <= @peakend then datediff (hh, @peakstart, endtime)
when starttime >= @peakstart and endtime >= @peakend then datediff (hh, starttime, @peakend)
when starttime >= @peakstart and endtime <= @peakend then datediff (hh, starttime, endtime)
else null end
from test1```

8. Registered User
Join Date
Aug 2012
Posts
6
Both work Thank you

9. Registered User
Join Date
Aug 2012
Posts
45
Originally Posted by evilInside
here is an example,

if the app was running for 45 hrs how can determine how many hrs were peek hrs and how many hrs were non peek
Stephen, after seeing this post, I believe the below is what you want. It could all be combined into one query, but I thought a separate CTE for each step would better illustrate what is happening. I have to do some actual work now, but after I will check it to make sure it is accurate.

Code:
```IF OBJECT_ID('tempdb..AppTimes','u') IS NOT NULL
DROP TABLE tempdb..Apptimes

CREATE TABLE tempdb..AppTimes
(
ID INT IDENTITY(1,1),
AppStart DATETIME NOT NULL,
AppStop DATETIME NOT NULL
)

INSERT INTO tempdb..AppTimes VALUES
('2010-11-08 10:47:20.117','2010-11-10 07:27:30.273'),
('2012-08-23 09:27:30.000','2012-08-24 00:30:30.000'),
('2012-08-01 14:30:30.000','2012-08-03 14:00:00.000')

DECLARE	@PeakStart	TIME,
@PeakStop	TIME

SELECT @PeakStart = '13:00:00',
@PeakStop = '19:00:00'

;
WITH T1 AS (
SELECT 1 AS N UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
T2 AS (SELECT a.N FROM T1 a, T1 b),
T3 AS (SELECT a.N FROM T2 a, T2 b),
Tally AS (SELECT	N = ROW_NUMBER() OVER(ORDER BY N) FROM T3),
AppDays AS -- Break the records into one row for each day
(
SELECT
ID,
AppStartDayN = CASE WHEN t.N = 1 THEN AppStart
END,
AppStopDayN  = CASE WHEN DATEADD(D,DATEDIFF(D,0,AppStart) + t.N,0) < AppStop
ELSE AppStop
END
FROM tempdb..AppTimes a INNER JOIN Tally t
ON t.N-1 <= DATEDIFF(d,AppStart,AppStop)
),
AppDays2 AS -- Add the peak start and stop times for each day
(
SELECT
ID,
AppStartDayN,
AppStopDayN,
[Day] = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY AppStartDayN)
FROM AppDays
),
Seconds As
(
SELECT
ID,
[Day],
AppStartDayN,
AppStopDayN,
PeakStartDayN,
PeakStopDayN,
[TotalTime(S)] = DATEDIFF(S,AppStartDayN,AppStopDayN),
[PeakTime(S)]  = CASE WHEN AppStartDayN <= PeakStartDayN AND
AppStopDayN >= PeakStopDayN
THEN DATEDIFF(S,PeakStartDayN,PeakStopDayN)
WHEN AppStartDayN > PeakStartDayN AND
AppStopDayN >= PeakStopDayN
THEN DATEDIFF(S,AppStartDayN,PeakStopDayN)
WHEN AppStartDayN > PeakStartDayN AND
AppStopDayN <= PeakStopDayN
THEN DATEDIFF(S,AppStartDayN,AppStopDayN)
ELSE 0
END
FROM AppDays2
)
SELECT
ID,
AppStart = MIN(AppStartDayN),
AppStop = MAX(AppStopDayN),
[TotalTime(H)] = SUM([TotalTime(S)])/3600,
[TotalTime(M)] = (SUM([TotalTime(S)]) % 3600)/60,
[TotalTime(S)] = (SUM([TotalTime(S)]) % 3600)%60,
[PeakTime(H)] = SUM([PeakTime(S)])/3600,
[PeakTime(M)] = (SUM([PeakTime(S)]) % 3600)/60,
[PeakTime(S)] = (SUM([PeakTime(S)]) % 3600)%60
FROM Seconds
GROUP BY ID```
Greg

10. Registered User
Join Date
Aug 2012
Posts
45
Ok, I suspect there may be crickets chirpin' over here by now... Anyhow, Stephen, I realized my code did not account for differences in minutes and seconds of the peak times. That may not be an issue for you, but this will fix it
Code:
```IF OBJECT_ID('tempdb..AppTimes','u') IS NOT NULL
DROP TABLE tempdb..Apptimes

CREATE TABLE tempdb..AppTimes
(
ID INT IDENTITY(1,1),
AppStart DATETIME NOT NULL,
AppStop DATETIME NOT NULL
)

INSERT INTO tempdb..AppTimes VALUES
('2010-11-08 10:47:20.117','2010-11-10 07:27:30.273'),
('2012-08-23 09:27:30.000','2012-08-24 00:30:30.000'),
('2012-08-01 14:30:30.000','2012-08-03 14:00:00.000')

DECLARE	@PeakStart	TIME,
@PeakStop	TIME

SELECT @PeakStart = '13:01:01',
@PeakStop = '19:01:01'

;
WITH T1 AS (
SELECT 1 AS N UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
T2 AS (SELECT a.N FROM T1 a, T1 b),
T3 AS (SELECT a.N FROM T2 a, T2 b),
Tally AS (SELECT	N = ROW_NUMBER() OVER(ORDER BY N) FROM T3),
AppDays AS -- Break the records into one row for each day
(
SELECT
ID,
AppStartDayN = CASE WHEN t.N = 1 THEN AppStart
END,
AppStopDayN  = CASE WHEN DATEADD(D,DATEDIFF(D,0,AppStart) + t.N,0) < AppStop
ELSE AppStop
END
FROM tempdb..AppTimes a INNER JOIN Tally t
ON t.N-1 <= DATEDIFF(d,AppStart,AppStop)
),
AppDays2 AS -- Add the peak start and stop times for each day
(
SELECT
ID,
AppStartDayN,
AppStopDayN,
[Day] = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY AppStartDayN)
FROM AppDays
),
Seconds As
(
SELECT
ID,
[Day],
AppStartDayN,
AppStopDayN,
PeakStartDayN,
PeakStopDayN,
[TotalTime(S)] = DATEDIFF(S,AppStartDayN,AppStopDayN),
[PeakTime(S)]  = CASE WHEN AppStartDayN <= PeakStartDayN AND
AppStopDayN >= PeakStopDayN
THEN DATEDIFF(S,PeakStartDayN,PeakStopDayN)
WHEN AppStartDayN > PeakStartDayN AND
AppStopDayN >= PeakStopDayN
THEN DATEDIFF(S,AppStartDayN,PeakStopDayN)
WHEN AppStartDayN > PeakStartDayN AND
AppStopDayN <= PeakStopDayN
THEN DATEDIFF(S,AppStartDayN,AppStopDayN)
ELSE 0
END
FROM AppDays2
)
SELECT
ID,
AppStart = MIN(AppStartDayN),
AppStop = MAX(AppStopDayN),
[TotalTime(H)] = SUM([TotalTime(S)])/3600,
[TotalTime(M)] = (SUM([TotalTime(S)]) % 3600)/60,
[TotalTime(S)] = (SUM([TotalTime(S)]) % 3600)%60,
[PeakTime(H)] = SUM([PeakTime(S)])/3600,
[PeakTime(M)] = (SUM([PeakTime(S)]) % 3600)/60,
[PeakTime(S)] = (SUM([PeakTime(S)]) % 3600)%60
FROM Seconds
GROUP BY ID```
Greg

11. Registered User
Join Date
Aug 2012
Posts
6
Thanks Greg. Yeah, I am not sure if I need it in minutes and seconds or not but I was having a problem with the days in your previous post. I still need to poke around with this a little more but i think this will work for me. Thanks you!

Stephen

12. Registered User
Join Date
Aug 2012
Posts
45
Originally Posted by evilInside
I was having a problem with the days in your previous post.
Yep, I did not see the part about multiple days until after I posted the first time, so you can just ignore that.

Greg

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•