Breaking Out 'TimeStamps & Total Duration' into 15 Minute Intervals

[Data1234]

Hello. I am trying to figure out how to take a table of timestamps and activity duration and break it into intervals, where it groups the duration into the time spent in each 15 minute interval. I have included a sample of the data and output. Is there any way to pull this in Access (or SQL)?

Raw Data
Employee	Activity	TimeStampStart	TimeStampEnd	TotalDuration
Doe,John	SomeActivity	12/16/13 9:06:02 AM	12/16/13 9:57:14 AM	0:51:12
Smith,Jane	OtherActivity	12/16/13 9:22:15 AM	12/16/13 10:06:55 AM	0:44:40
Query to break out the total duration time in to the 15 minute interval it fell into
Employee	Activity	Interval	IntervalDuration
Doe,John	SomeActivity	12/16/13 9:00:00 AM	0:08:58
Doe,John	SomeActivity	12/16/13 9:15:00 AM	0:15:00
Doe,John	SomeActivity	12/16/13 9:30:00 AM	0:15:00
Doe,John	SomeActivity	12/16/13 9:45:00 AM	0:12:14
Smith,Jane	OtherActivity	12/16/13 9:15:00 AM	0:07:45
Smith,Jane	OtherActivity	12/16/13 9:30:00 AM	0:15:00
Smith,Jane	OtherActivity	12/16/13 9:45:00 AM	0:15:00
Smith,Jane	OtherActivity	12/16/13 10:00:00 AM	0:06:55

Thanks!!!

[Dal Jeanis]

1) Set up a table tblInterval with values for the interval start and end. Put as many records as you might ever need, and no more. 24 hours worth is fine but preferably hold it to teh max length of an activity. The first three records look like this -

tblIntervals
  IntStart  Number in minutes
  IntEnd    Number in minutes


   IntStart IntEnd
   0        15
   15       30
   30       45

tblRawData
   Employee
   Activity
   TimeStampStart  Date/Time
   TimeStampEnd    Date/Time

To turn your timestampstart into the first interval, we multiply by 96, then take the integer, then divide by 96 again. This is because +1 in a timestamp is 24 hours, so +1/24 is 1 hour, and +1/96 is 15 minutes. The integer function kills all the unneeded minutes and seconds.

Then, we add the start minute (0, 15,30 etc) of each interval to the resulting base interval.

Select
   TR.Employee,
   TR.Activity,
   DateAdd("n",TI.IntStart,(Int(96*TR.TimeStampStart))/96 ) AS Interval,
   DateAdd("n",TI.IntEnd,(Int(96*TR.TimeStampStart))/96 ) AS IntervalEnd,
   (IIF(IntervalEnd>TR.TimeStampEnd,TR.TimeStampEnd,IntervalEnd) - 
   IIF(Interval<TR.TimeStampStart,TR.TimeStampStart,Interval)) AS IntervalDuration
FROM
   tblRawData AS TR,
   tblIntervals AS TI
WHERE
   (DateAdd("n",TI.IntStart,(Int(96*TR.TimeStampStart))/96 ) < TR.TimeStampEnd))

The duration of the interval is the earlier of the end of the timestamp or the end of the interval, minus the later of the start of the timestamp or the start of the interval. You may have to cast that calculation as a date with the CDate() function, I'm not sure.