hourly counts in crystal

I need to get hourly counts on the half hour to the end of the attached report.  What is the easiest way to accomplish this? The count will be when the bollard is up.  The dots are filler.  I need to show each hour.

example

04/01/2015

00:30 - 01:30 5 times
01:30 - 02:30 0 times
...
...
...
15:30 - 16:30 25 times
...
...
...
23:30 - 00:30 2 times

04/02/2015

00:30 - 01:30 10 times
01:30 - 02:30 1 times
...
...
...
15:30 - 16:30 25 times
...
...
...
23:30 - 00:30 1 times
nmnh-daily-bollard-report-wip-1.pdf
Thanks_for_your_help_EveryoneAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

vastoCommented:
Create a table, for example TimePeriod, with 2 columns for the beginning and the end of the time period. Join this table with your data based on the time you have in your data falling in the period from the new table and group by the fields in TimePeriod.

Sample SQL will look like this

SELECT tp.StartTime, tp.EndTime, Count(*)
FROM TimePeriod rp INNER JOIN YourTable yt ON rp.StartTime<=yt.Time and yt.Time<rp.EndTime
GROUP BY  tp.StartTime, tp.EndTime

You do not need to use SQL you can create group in Crystal
0
Thanks_for_your_help_EveryoneAuthor Commented:
Can you give me an example so I can be at least be pointed in the right direction.  All calculations need to be done in CR.

Thanks
0
vastoCommented:
Create the new table (TimePeriod) and join it in crystal with your data, then create a formula, which combines StartTime,  and EndTime and group by this formula
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Thanks_for_your_help_EveryoneAuthor Commented:
Sorry, still not sure what you mean to do.  Can you show me a quick formula that will at least get me pointed in the right direction.  I know enough about CR to be a little dangerous ;).  Even though I have written multiple reports, none with this much difficulty.
0
vastoCommented:
May be it will be easier if you just create a command and copy paste the SQL there :
SELECT tp.StartTime, tp.EndTime, Count(*)
 FROM TimePeriod rp INNER JOIN YourTable yt ON rp.StartTime<=yt.Time and yt.Time<rp.EndTime
 GROUP BY  tp.StartTime, tp.EndTime
0
James0628Commented:
Do you want to see a line on the report for the time periods when you (presumably) have no data?  For example, on the first page in your PDF file, you have lines for 4/15/2015 4:08:23 PM and 4/15/2015 6:13:48 PM.  Do you want to see a line for the 4:30 - 5:30 time period that would fall in between those 2 lines?

 James
0
Thanks_for_your_help_EveryoneAuthor Commented:
Right now I have the report grouping by hour on the hour.  It has been requested to have it grouped on the 1/2 hour on the hour.  2:30 - 3:30, 3:30 - 4:30,  etc.  Is there a way to add an 1/2 hour to the Crystal default.
0
James0628Commented:
You can sort of shift the grouping.  I assume that you have a datetime field (not just the time).  Create a formula like this (let's call it Adjusted_time):

DateAdd ("n", -30, {your field})

 That just shifts the time back 30 minutes, so, for example, 8:15 AM will be changed to 7:45 AM, which puts it in the previous hour group.

 Change your group to use that formula, instead of the field.  Still grouped by the hour.  Now, for example, times from 8:30 to 9:29:59 will be changed to 8:00 to 8:59:59, so they'll be grouped together.

 You can use the group name to display the range (eg. 1:30 - 2:30).  The group name will be a string with the start of each hour.  The formula below converts the string back to a datetime, and adds 30 minutes back to it (eg. 1:00 becomes 1:30):

DateAdd ("n", 30, DateTime (GroupName ({@Adjusted_time}, "by hour")))

 Add 90 minutes to get the end time (eg. 1:00 becomes 2:30):

DateAdd ("n", 90, DateTime (GroupName ({@Adjusted_time}, "by hour")))

 I hope that made sense.  :-)  I'm pressed for time at the moment.

 James
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Thanks_for_your_help_EveryoneAuthor Commented:
This seems to make sense.  I created the formula but I am not sure how to change the group to use the formula.  Every time I tried I get errors or the wrong counts or it screws up the formatting.  I have uploaded the report for your review.
NMNH-DAILY-BOLLARD-REPORT-WIP-4.rpt
nmnh-daily-bollard-report-wip-4.pdf
0
James0628Commented:
I don't see a formula that's subtracting 30 minutes in that report, but once the formula is created, go to Report > Group Expert, select the datetime group, click Options, and then select the formula from the dropdown list to change the group to use that formula, instead of the field.

 Since you have two groups on the datetime, one for each day and one for each hour, you'd need to change both groups to use the formula.

 Try that and see what you get.

 James
0
Thanks_for_your_help_EveryoneAuthor Commented:
Followed your instructions and now my count field now has an error.

Count ({JOURNAL1.PANELLOCALTZDT}, {JOURNAL1.PANELLOCALTZDT}, "by hour")*.5

There must be a group that matches this field.

Tried using the date add formula and it did not work.
0
James0628Commented:
The "must be a group" error is because the second argument to Count has to be something that you're grouping on (the formula, in this case).  If you replace the second JOURNAL1.PANELLOCALTZDT with the formula name, that should get rid of that error.

 You could replace the first JOURNAL1.PANELLOCALTZDT too, but since you're just doing a count, it doesn't really matter.

 James
0
Thanks_for_your_help_EveryoneAuthor Commented:
ok that worked thanks.  Now onto the last part.  Where do I insert this formula at so the group name will be correct. right now I see for example  "4/17/2015 12:00PM = 39"  Need to see  "4/17/2015 12:30PM = 39"

You can use the group name to display the range (eg. 1:30 - 2:30).  The group name will be a string with the start of each hour.  The formula below converts the string back to a datetime, and adds 30 minutes back to it (eg. 1:00 becomes 1:30):

 DateAdd ("n", 30, DateTime (GroupName ({@Adjusted_time}, "by hour")))

  Add 90 minutes to get the end time (eg. 1:00 becomes 2:30):

 DateAdd ("n", 90, DateTime (GroupName ({@Adjusted_time}, "by hour")))
0
James0628Commented:
Create a formula (call it whatever you like) like this:

DateAdd ("n", 30, DateTime (GroupName ({@Adjusted_time}, "by hour")))

 If the formula that you used for the group isn't named @Adjusted_time, change that to your formula name.

 If you put that formula on the report, you should get the altered time.  All it does is convert the group name string (eg. "4/17/2015 12:00PM") back to a datetime, and add 30 minutes to that.  Using your example, you should get 4/17/2015 12:30PM, instead of 4/17/2015 12:00PM.  You can drop that formula in  a field with your count.

 James
0
Thanks_for_your_help_EveryoneAuthor Commented:
YES that worked.  Thanks.  This isn't as important but if easy, can I show the end time using the 2nd formula.(DateAdd ("n", 90, DateTime (GroupName ({@Adjusted_time}, "by hour")))) When I try to add to the same @TIME_START I get an error.  Like to have something like this.

4/17/2015 12:30PM - 01:30PM
0
James0628Commented:
I don't know what @TIME_START is, but the DateAdd formula should work just like the other one.  If you want the time, without the date, just put the whole thing in a Time function.

Time (DateAdd ("n", 90, DateTime (GroupName ({@Adjusted_time}, "by hour"))))

 James
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.