Split datetime records with 5 minute intervals

All replies

• 

  

  0

  Sign in to vote

  Patrick:

   

  Here is a post in which they look at 30 minute intervals.  Note that the interval period is a variable so you should be able to readily change this to 5-minute intervals:

   

  http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=81913&SiteID=1

   

   Looking at it more closely you will probably need more than this.

  Monday, April 2, 2007 2:57 PM
• 

  

  0

  Sign in to vote

  Hi

   

  Here are a couple of solutions.  You can only use the common table expression solution if you have SQL Server 2005.

  
  

  Code Snippet

  DECLARE @TestTable table
  (
     RecID int,
     StartDate datetime,
     EndDate datetime
  )
  
  SET NOCOUNT ON
   
  INSERT INTO @TestTable Values ( 1, '2007-01-01 09:00:00', '2007-01-01 09:10:00' )
  INSERT INTO @TestTable Values ( 2, '2007-01-01 09:15:00', '2007-01-01 09:25:00' )
  INSERT INTO @TestTable Values ( 3, '2007-01-02 12:00:00', '2007-01-02 12:30:00' )
  INSERT INTO @TestTable Values ( 4, '2007-01-03 06:00:00', '2007-01-03 06:12:00' )
  
  -- USING A RECURSIVE COMMON TABLE EXPRESSION (CTE)
  --------------------------------------------------
  ;WITH DateList (RecID, StartDate, EndDate) AS
  (
      SELECT RecID, StartDate,
          CASE
              WHEN (DATEADD(mi, 5, startdate) <= enddate) THEN
                  DATEADD(mi, 5, startdate)
              ELSE EndDate
          END
      FROM @TestTable
  UNION ALL
      SELECT TT.RecID, DL.EndDate,
          CASE
              WHEN (DATEADD(mi, 5, DL.enddate) <= TT.enddate) THEN
                  DATEADD(mi, 5, DL.enddate)
              ELSE TT.EndDate
          END
      FROM @TestTable TT
          INNER JOIN DateList DL
              ON (TT.RecID = DL.RecID)
      WHERE (DL.enddate < TT.enddate)
  )
  SELECT *
  FROM DateList
  ORDER BY RecID, StartDate
  
  -- NON-CTE SOLUTION
  -------------------
  DECLARE @TimePeriods table
  (
     TimeDiff int
  )
  
  INSERT INTO @TimePeriods Values ( 5 )
  INSERT INTO @TimePeriods Values ( 10 )
  INSERT INTO @TimePeriods Values ( 15 )
  INSERT INTO @TimePeriods Values ( 20 )
  INSERT INTO @TimePeriods Values ( 25 )
  INSERT INTO @TimePeriods Values ( 30 )
  INSERT INTO @TimePeriods Values ( 35 )
  INSERT INTO @TimePeriods Values ( 40 )
  INSERT INTO @TimePeriods Values ( 45 )
  INSERT INTO @TimePeriods Values ( 50 )
  INSERT INTO @TimePeriods Values ( 55 )
  INSERT INTO @TimePeriods Values ( 60 )
  
  -- Multi join to Time Periods table
  SELECT A.RecID,
      StartDate = DATEADD(mi, B.TimeDiff - 5, A.StartDate),
      EndDate = DATEADD(mi, B.TimeDiff, A.StartDate)
  FROM (
          SELECT RecID, StartDate, PeriodLen = DATEDIFF(mi, StartDate, EndDate)
          FROM @TestTable
      ) A
      INNER JOIN @TimePeriods B
          ON (A.PeriodLen >= B.TimeDiff)
  ORDER BY A.RecID, B.TimeDiff

   

  The CTE expression will as the sample shows deal with period that are not an exact multiple of 5 minutes long.

   

  The non-cte version requires a table with a record for every increment of 5 minutes that a period can have (if they can be up to 4 hours long you need 5 to 240 in increments of 5 in it).  This is used to generate the records.  Remove the yellow marked lines (between the two comments) to get the non-cte version to work on SQL Server 2000

   

  Monday, April 2, 2007 3:46 PM
• 

  

  0

  Sign in to vote

  Ooops.

   

  Here's a modified version of the non-CTE query which will also deal with non-5 minute multiple intervals

   

  
  

  Code Snippet

  -- Multi join to Time Periods table
  SELECT A.RecID,
      StartDate = DATEADD(mi, B.TimeDiff - 5, A.StartDate),
      EndDate = CASE
          WHEN DATEADD(mi, B.TimeDiff, A.StartDate) <= A.EndDate
              THEN DATEADD(mi, B.TimeDiff, A.StartDate)
          ELSE
              A.EndDate
      END
  FROM (
          SELECT RecID, StartDate, EndDate,
              PeriodLen = DATEDIFF(mi, StartDate, EndDate)
          FROM @TestTable
      ) A
      INNER JOIN @TimePeriods B
          ON ((A.PeriodLen + 5) > B.TimeDiff)
  ORDER BY A.RecID, B.TimeDiff
  

   

  Didn't occur to me to update it to do that until after I had posted the other note.

   

  The queries do work with intervals which do not start on 5 minute boundaries (but the increments do not either).  It will have problems if the either there are two records with the same ID or the start time is later than the end time.

   

  If the start and end time are the same then the CTE will put one record in like that and the non-CTE version will put no record in.

  Monday, April 2, 2007 3:56 PM
• 

  

  0

  Sign in to vote

  Maybe a better approach in this case is to establish (1) a calendar table and (2) an interval table.  Information related to a calendar table can be found here:

   

  http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

  I put together a mockup of a query using both an calendar and an interval table.  With this mockup I used a SMALL_ITERATOR table to populate these tables.  A definition of this table can be found here:

   

  http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1330536&SiteID=1

   

  Here are the CALENDAR and INVERVAL tables that I used in the mockup:

   

  create table dbo.interval
     (    rid             int       not null
            constraint pk_interval primary key,
          fromTime        datetime,
          toTime          datetime
     )
  go

   

  insert into interval
  select iter,
         dateadd (mi, 5*(iter-1), ''),
         dateadd (mi, 5*iter,     '')
    from small_iterator (nolock)
  where iter <= 288

   

  create table dbo.calendar
     (    calDate         datetime
            constraint pk_calendar primary key
     )
  go
  

  insert into dbo.calendar
  select cast('12/31/2006' as datetime) + iter
    from small_iterator (nolock)
  where iter <= 365

  The query I used to test with is:

   

  declare @intervalTest table
     (    rid             int,
          loDateTime      datetime,
          hiDateTime      datetime
     )

  insert into @intervalTest
  select 1,    '01/01/2007 09:00:00',     '01/01/2007 09:10:00' union all
  select 2,    '01/01/2007 09:15:00',     '01/01/2007 09:25:00' union all
  select 3,    '02/01/2007 12:00:00',     '02/02/2007 12:30:00' union all
  select 4,    '02/03/2007 10:09:00',     '02/03/2007 10:16:00'

   

  select a.rid,
         calDate + fromTime as [From date/time],
         calDate + toTime as [To date/time]
    from @intervalTest a
   inner join calendar b
      on cast(floor(cast(a.loDateTime as float)) as datetime) <= b.calDate
     and a.hiDateTime >  b.calDate
   inner join interval c
      on b.calDate + fromTime >= loDateTime
     and b.calDate + fromTime <  hiDateTime

   

  /*
  rid    From date/time             To date/time 
  ------ -------------------------- -----------------------
  1      2007-01-01 09:00:00.000    2007-01-01 09:05:00.000
  1      2007-01-01 09:05:00.000    2007-01-01 09:10:00.000
  2      2007-01-01 09:15:00.000    2007-01-01 09:20:00.000
  2      2007-01-01 09:20:00.000    2007-01-01 09:25:00.000
  ...
  3      2007-02-02 12:25:00.000    2007-02-02 12:30:00.000
  4      2007-02-03 10:10:00.000    2007-02-03 10:15:00.000
  4      2007-02-03 10:15:00.000    2007-02-03 10:20:00.000
  */
  

   

  Monday, April 2, 2007 4:14 PM
• 

  

  0

  Sign in to vote

  Thanx very much for the replies. I was ill for a week so I couldn't post my thank you's any earlier, but the post helped me very well.

   

  Greetings,

   

  Patrick

  Tuesday, April 10, 2007 9:44 AM
• 

  

  0

  Sign in to vote

  Hi,

   

  The solution provided by Kent worked, except for one thing. If for example a time period is from:

   

  01/01/2007 21:56 until 01/01/2007 23:05 all intervals are created perfect except for the first one. The table then looks as follows:

   

  01/01/2007 22:00 - 01/01/2007 22:05

   

  I would like to have as the first interval the following one => 01/01/2007 21:55 - 01/01/2007 22:00

   

  Could someone help me solve this please. Again sorry for the late feedback but I was ill for a week.

   

  Greetings,

   

  Patrick

  Wednesday, April 11, 2007 9:57 AM
• 

  

  0

  Sign in to vote

  I already solved it myselve.

   

  Greetings,

   

  Patrick

  Wednesday, April 11, 2007 1:50 PM