Summing Times

[ptrifile]

I am trying to create a report that sums each 15 minute interval for an entire day. I have a time field that shows me activity for any given minute, but i would like it to just show sums for every 15 minute time period I would like to have the output look like the following:

7/1/2009 00:00-00:15 100
7/1/2009 00:15-00:30 110
7/1/2009 00:30-00:45 95
7/1/2009 00:45-01:00 150

and so on....

instead of showing:

7/1/2009 00:00:01 50
7/1/2009 00:00:10 25
7/1/2009 00:00:15 25



Is this something that can be accomplished relatively easily?

any help or suggestions would be greatly appreciated.

Paul

 

[lbass]

Create a formula like this:

if minute({table.datetime}) < 15 then
"00 to 14" else
if minute({table.datetime}) < 30 then
"15 to 29" else
if minute({table.datetime}) < 45 then
"30 to 44" else
"45 to 59"

Insert a group first on {table.datetime} on change of date and then a second time on change of hour. Then insert a group on the above formula. Then you can insert a summary on your amount field at the Group #3 level.

-LB

 

[ptrifile]

Thanks lbass, that gets me closer, i think i made a typo on my initial post. I need to have four 15 minute intervals each hour. So for every hour of the day i need to sum each individual 15 minute period.

Looking at the forumula you suggested would i be able to simply change the "minute" to "hour" and get the same result by listing all of the time frames of the day?


Thanks again,


Paul

 

[lbass]

No, you need to insert a group on datetime on change of hour. Please see my original response. If you have a group on hour, and then a group on the formula, you will have 15-minute intervals within each hour--as long as there is data in each interval.

-LB

 

[ptrifile]

lbass, that worked out perfectly! thank you so much. I appologize i completelely missed reading the last part of your post.

One more thing, if there is no data for a particular 15 minute increment of any given hour, how can i get it to disply a zero?

Thanks again for all your help! you are a life saver!!!!

 

[lbass]

That's more complex. You would have to use a left join FROM a table that contains all possible datetimes (or at least with one record within each 15-minute interval) to the other tables, with no selection criteria on the right hand tables.

-LB