Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1153
  • Last Modified:

Count function in Crystal

Hi.  I need to do a simple record count in CR.
Fields: an integer, a datetime stamp.
I need a count by day/date/year of events each day:
5813      10/24/2008     7
5813       10/25/2008    6

SQL is a cinch:

SELECT     didnumber,LEFT(CONVERT(varchar(11), starttime, 101),11) as start
into temp1
FROM         CallLog
WHERE     (DIDNumber IN (5290, 5813))

select didnumber,start,count(*) total
from temp1
group by didnumber,start
ORDER BY DIDNumber, Start

How is this done in CR Design?
Thanks.
0
michaelheffernan
Asked:
michaelheffernan
  • 6
  • 4
1 Solution
 
mlmccCommented:
You can group the report by date 3 times
For the first select the timeframe as YEAR
Second group timeframe is month
third timeframe is day

Then use summaries at the group level to count the events.

mlmcc
0
 
michaelheffernanAuthor Commented:
Hmmmmm.... lemme think about this 'un...
0
 
michaelheffernanAuthor Commented:
Hi.  Well, I kinda get the logic.  I don't see how to parse the year/month/day out of this datetime field for each grouping.  CR wants to group the field, period.

How do I parse out each element of the datetime field?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
mlmccCommented:
When you create the group, since it is a date field you get an extra option for the interval to group on.

mlmcc
GroupingByDate.jpg
0
 
michaelheffernanAuthor Commented:
Ah, different version.
 
I do not have such options.  I so rarely have to do something like this that I have never upgraded CR thru its ownership changes.
This may not be possible with this version.

1.jpg
0
 
mlmccCommented:
You need to choose a DATE field.

mlmcc
0
 
michaelheffernanAuthor Commented:
Ah, oops, yes.  Okay, lemme try it...
0
 
michaelheffernanAuthor Commented:
Ah, I see, I see, sayeth the blind man.  Okay, thanks, I believe I can sort this out; thank you.
0
 
mlmccCommented:
WHy accept that comment as the answer when the comment from the expert is the answer.  

mlmcc
0
 
michaelheffernanAuthor Commented:
Thank you.  I probably clicked the wrong link or wrong comment to accept as a solution.
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now