Results 1 to 12 of 12
  1. #1
    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. #2
    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. #3
    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. #4
    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. #5
    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
    	TotalTime = CAST(DATEADD(S,[TotalTime(S)],CAST(DATEADD(MI,[TotalTime(M)],CAST(DATEADD(HH,[TotalTime(H)],0) AS TIME)) AS TIME)) AS TIME),
    	PeakTime = CAST(DATEADD(S,[PeakTime(S)],CAST(DATEADD(MI,[PeakTime(M)],CAST(DATEADD(HH,[PeakTime(H)],0) AS TIME)) AS TIME)) AS TIME),
    	OffPeak = CAST(DATEADD(S,[TotalTime(S)] - [PeakTime(S)],CAST(DATEADD(MI,[TotalTime(M)] - 
    				[PeakTime(M)],CAST(DATEADD(HH,[TotalTime(H)] - [PeakTime(H)],0) AS TIME)) AS TIME)) AS TIME) 
      FROM Times
    Greg

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

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    How about:
    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. #8
    Join Date
    Aug 2012
    Posts
    6
    Both work Thank you

  9. #9
    Join Date
    Aug 2012
    Posts
    45
    Quote Originally Posted by evilInside View Post
    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
    								ELSE DATEADD(D,DATEDIFF(D,0,AppStart) + t.N-1,0)
    							END,
    			AppStopDayN  = CASE WHEN DATEADD(D,DATEDIFF(D,0,AppStart) + t.N,0) < AppStop
    								THEN DATEADD(D,DATEDIFF(D,0,AppStart) + t.N,0)
    								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,
    			PeakStartDayN = DATEADD(HH,DATEPART(HH,@PeakStart),DATEADD(d,DATEDIFF(d,0,AppStartDayN),0)),
    			PeakStopDayN = DATEADD(HH,DATEPART(HH,@PeakStop),DATEADD(d,DATEDIFF(d,0,AppStartDayN),0)),
    			[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. #10
    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
    								ELSE DATEADD(D,DATEDIFF(D,0,AppStart) + t.N-1,0)
    							END,
    			AppStopDayN  = CASE WHEN DATEADD(D,DATEDIFF(D,0,AppStart) + t.N,0) < AppStop
    								THEN DATEADD(D,DATEDIFF(D,0,AppStart) + t.N,0)
    								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,
    			PeakStartDayN = DATEADD(S,DATEPART(S,@PeakStart),DATEADD(MINUTE,DATEPART(MINUTE,@PeakStart),DATEADD(HH,DATEPART(HH,@PeakStart),DATEADD(d,DATEDIFF(d,0,AppStartDayN),0)))),
    			PeakStopDayN = DATEADD(S,DATEPART(S,@PeakStop),DATEADD(MINUTE,DATEPART(MINUTE,@PeakStop),DATEADD(HH,DATEPART(HH,@PeakStop),DATEADD(d,DATEDIFF(d,0,AppStartDayN),0)))),
    			[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. #11
    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. #12
    Join Date
    Aug 2012
    Posts
    45
    Quote Originally Posted by evilInside View Post
    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
  •