Help with Time Increment results

• thomashohner

  SSCrazy Eights

  Points: 9856

  More actions

  May 24, 2017 at 9:23 am

  #337233

  I have a table with a start time and duration of minutes. I need to convert it to a equal increment as shown in the desired results. I started using a Tally Table with 15 minute increments but i'm finding it hard to limit the start and stop times to stay within the min and max times of the value set. I was looking to see if some one had some clever way of expanding out the result set into equal increments.
  Thanks !!

  
CREATE TABLE #TimeIncrements (StartDate DATETIME, Duration INT)

  INSERT INTO #TimeIncrements VALUES
('2017-05-24 08:00:00.000',    '15')
,('2017-05-24 08:15:00.000',    '45')
,('2017-05-24 09:00:00.000',    '15')
,('2017-05-24 09:15:00.000',    '45')
,('2017-05-24 10:00:00.000',    '15')

  SELECT *
FROM #TimeIncrements

  /*
Desired result

  2017-05-24 08:00:00.000    15
2017-05-24 08:15:00.000    15
2017-05-24 08:30:00.000    15
2017-05-24 08:45:00.000    15
2017-05-24 09:00:00.000    15
2017-05-24 09:15:00.000    15
2017-05-24 09:30:00.000    15
2017-05-24 09:45:00.000    15
2017-05-24 10:00:00.000    15

  */
DROP TABLE #TimeIncrements

  ***SQL born on date Spring 2013:-)

• Joe Torre

  SSChampion

  Points: 10248

  More actions

  May 24, 2017 at 11:35 am

  #1944045

  Try:
  

  DECLARE @d datetime = '2017-05-24 08:00:00.000';

  WITH tally(n)

  AS (

  SELECT

  t.n

  FROM

  ( VALUES ( 0), ( 1), ( 2), ( 3), ( 4), ( 6), ( 7), ( 8), ( 9) ) t (n)

  ) ,

  tally2

  AS (

  SELECT

  Row_Number() OVER (ORDER BY t4.n) Rn

  FROM

  tally t1

  CROSS APPLY tally t2

  CROSS APPLY tally t3

  CROSS APPLY tally t4

  )

  SELECT

  @d Dt

  UNION

  SELECT

  DateAdd(MINUTE, tally2.Rn, @d)

  FROM

  tally2

  WHERE

  tally2.Rn <= 960

  AND tally2.Rn % 15 = 0

  AND DateAdd(MINUTE, tally2.Rn, @d)<=DateAdd(HOUR, 8, @d);

  
  
  

• jonathan.crawford

  SSCertifiable

  Points: 6613

  More actions

  May 24, 2017 at 12:49 pm

  #1944059

  Nice Joe! We have a function to do this, sourced from Jeremiah Peschka (http://facility9.com/2010/02/24/rounding-to-the-nearest-x-minutes), which works nicely when we need it, and allows for choosing your interval a little more. I like the Tally alternative though, added that to my briefcase.

  -------------------------------------------------------------------------------------------------------------------------------------
  Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses