Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Evaluate the first record of a group

Posted on 2010-08-26
16
Medium Priority
?
1,183 Views
Last Modified: 2012-05-10
I have a database that has three columns: a date/time, a person's name and a ticket number.  I have grouped my report on the ticket number.  I need to evaluate only the first row of the group, based on the date/time, and count how many times each person's name shows up.   The database would look like this:

7/2/2010 11:35:25  JACK  12345
7/2/2010 11:40:25  JANE  12345
7/2/2010 11:45:25  JANE  12345

7/2/2010 11:50:25  EMILY  12346
7/2/2010 11:55:25  JACK  12346

7/2/2010 12:00:25  JACK  12347
7/2/2010 12:05:25  JANE  12347

7/2/2010 12:10:25  JANE  12348

and the results would look like this:
JACK  EMILY  JANE
  2          1           1


Any ideas?
0
Comment
Question by:rainfields
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +2
16 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 33535456
Do you need the "extra" records in the report?

I think you could use the TOP 1 option on the group

If you need all the data, is there a limit on the number of names?
You will need to do this with variables and arrays.

The other way would be to use a subreport that selects only the first record and do the table in the subreport

mlmcc
0
 

Author Comment

by:rainfields
ID: 33537365
I do not need the extra records, just the first one in each group.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 33537597
I would use a database command as the source and use the TOP 1 option to select only the first record in each group

mlmcc
0
On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

 
LVL 2

Expert Comment

by:bischofb_CRNET
ID: 33546139
I've done this a hundred times. Use a command object and a simple SQL statement like this (insert your own field and table names):

SELECT person_name, Count(*) FROM your_table GROUP BY person_name

To print it out across the page, use a label format that goes from left to right. Should work fine.

If you find this helpful, please mark the question answered.  :-)
0
 
LVL 2

Expert Comment

by:bischofb_CRNET
ID: 33546158
Oh - I forgot that you are grouping by ticket number.

SELECT Ticket_number, person_name, Count(*) FROM your_table GROUP BY ticket_number, person_name

0
 

Author Comment

by:rainfields
ID: 33546746
mlmmc: could you give me more information on how you would use the TOP 1 to get rid of all but the first record in each group?
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 33547536
My SQL is rusty so I really can't

It is something like

SELECT TOP 1 name FROM database Group By somefield

mlmcc
0
 
LVL 2

Expert Comment

by:Venkatgvi
ID: 33548557
what database are you using, if it is oracle, just use this query

select ticket_no,name, count(name) over (partition by name) name_count from <table_name>
0
 
LVL 2

Expert Comment

by:bischofb_CRNET
ID: 33562580
Here is how I would do it. First off, you only want to see the first record in each group by date. So you will need to create a formula in the group header b/c that will let you isolate only the first record. Second, you want to do it by date, so you will need to add a sort field based on the date.

Now that you have isolated the first record, and you know it is the most recent one, you need to create a formula to track how many of each item there is. To do this you will need two arrays. The first array tracks the name and the second array tracks the count. Create a formula that loops through the first array and checks if the name is in there or not. If not, then add it to the end of the array. If it is already there, then don't do anything. You will need to store the location of the index in where the name is found in the first array.

The second part of the formula uses the index from above and increases the count in the second array of the proper element. Thus, if the index for Emily is 2 (NumberVar NameIndex), then the second formula would be

CountArray[NameIndex] := CountArray[NameIndex} + 1;

When the report is finished, the first array will be filled with all the names and the second array will be filled with their total counts. Then in the report footer print each element in the arrays to show each name and its count.

-Brian
0
 

Author Comment

by:rainfields
ID: 33570839
Brian, thanks for the input.  That gets me most of the way, but I am still having problems with the formula that would isolate the first record.  Any help would be appreciated.  The TOP 1 suggestion that mlmcc had isn't working for me.
0
 
LVL 2

Accepted Solution

by:
bischofb_CRNET earned 1000 total points
ID: 33571453
Ok, I'm going to type this in off the top of my head since I don't have your report here. So there might be a syntax error, but for the most part it should work fine. Replace the dummy field names with yours where appropriate. Put this formula in the group header.

//create the array without losing older values
Global NumberVar Array NameArray;
Redim Preserve NameArray[100];  // I assume you wont' have more than 100 names?
Redim Preserve CountArray[100]
NumberVar SearchIndex;
NumberVar NameIndex;

StringVar NameToFind;
NameToFind := {yourtable.namefield};

SearchIndex := 1;
//Loop until we find a match
While NameIndex == 0
(
    If NameArray[SearchIndex] == NameToFind Then
        //Found the name!
        NameIndex=SearchIndex;
    If NameArray[SearchIndex] == "" Then
    (
        //We are at the end and didn't find it. So add it to the current postiion
        NameArray[SearchIndex] := NameToFind;
        NameIndex := SearchIndex;
    )
    SearchIndex := SearchIndex + 1;
)

//Increase the counter by one
CountArray[NameIndex] := CountArray[NameIndex] + 1;
0
 
LVL 2

Expert Comment

by:bischofb_CRNET
ID: 33571465
I forgot to declare the second array at the top. Here they are again:

Global NumberVar Array NameArray;
Global NumberVar Array CountArray;
0
 
LVL 35

Assisted Solution

by:James0628
James0628 earned 1000 total points
ID: 33573873
Brian's basic idea should work.  I would write the formula a bit differently, but I think his will work too, but there are a few syntax and other issues.

//create the array without losing older values
Global NumberVar Array NameArray;
Global NumberVar Array CountArray;
Redim Preserve NameArray[100];  // I assume you wont' have more than 100 names?
Redim Preserve CountArray[100];       // --- Added a ; here
NumberVar SearchIndex;
NumberVar NameIndex;

StringVar NameToFind;
NameToFind := {yourtable.namefield};

SearchIndex := 1;
//Loop until we find a match
While NameIndex = 0       // --- Replaced == with =
(
    If NameArray[SearchIndex] = NameToFind Then       // --- Replaced == with =
        //Found the name!
        NameIndex := SearchIndex;       // --- Replaced = with :=
    If NameArray[SearchIndex] = "" Then       // --- Replaced == with =
    (
        //We are at the end and didn't find it. So add it to the current postiion
        NameArray[SearchIndex] := NameToFind;
        NameIndex := SearchIndex;
    );       // --- Added a ; here
    SearchIndex := SearchIndex + 1;
);       // --- Added a ; here

//Increase the counter by one
CountArray[NameIndex] := CountArray[NameIndex] + 1;

// --- Added "" at the end, so that the formula doesn't produce
// --- any actual output on the report.
// --- That's really just a personal preference
""


 In addition to that, you should create a separate formula like the following and put it in the report header to initially create those variables.  It seems that global variables sometimes don't work properly if they're not declared first in the report header.

Global NumberVar Array NameArray;
Global NumberVar Array CountArray;


 If you could have more than 100 names on the report, you could increase the array sizes accordingly, or the formula could be changed to expand the arrays if necessary.  However, CR arrays are limited to 1000 elements, so if you could have more than 1000 names on a report, that will be a problem.

 James
0
 
LVL 35

Expert Comment

by:James0628
ID: 33573879
Oops.  Forgot to end that second formula (the one that goes in the report header) with "".  Again, that's just a personal preference of mine.  You could also just suppress the formula, or the entire section, if there's nothing in it that you need to see.

 James
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 34308560
This question has been classified as abandoned and is being closed as part of the Cleanup Program.  See my comment at the end of the question for more details.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question