Split Time Duration Into Hourly Intervals (T-SQL)


http://social.msdn.microsoft.com/Forums/en-US/1b96351f-d4fb-4cf4-ba24-07b2fb5f5007/cartesian-time-data-across-multiple-hourly-rows?forum=transactsql

 
  create table test (EventTime    datetime,     EventLengthSeconds int)
  insert into test values ('2014-04-25 13:31:16.857', 6657)

  ;with mycte as 
  (
  select EventTime as EventTime1, number ,EventLengthSeconds
  ,datediff(second,Eventtime,DATEADD(hour, DATEDIFF(hour,0,EventTime)+number+1,0))  as EventLengthSeconds1
  ,DATEADD(hh, DATEDIFF(hh,0,EventTime)+number+1,0) as EventTime2 
  ,EventLengthSeconds -(number-1)*3600-datediff(second,Eventtime,DATEADD(hour, DATEDIFF(hour,0,EventTime)+1,0)) as EventLengthSeconds2
   FROM [master].[dbo].[spt_values],test
  where type='p'  and (EventLengthSeconds*1./3600)+1>=number
  )

  select  
  case when number=0 then EventTime1 else EventTime2 end EventTime,
  case 
  when number=0 then EventLengthSeconds1
  when EventLengthSeconds2>=3600 then 3600
  when EventLengthSeconds2<3600 then EventLengthSeconds2
  end as EventLengthSeconds

  from mycte



--Clean up
DROP TABLE test		

 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s