Removing Duplicates from Groups

[MollyH]

Using CRXI and a SQL2K8 db.

This seems pretty simple, but for the life of me, I can't figure it out - searched the forum so if I missed the answer, I apologize for the clutter.

Trying to return and summarize 1 record per group.

3 fields: App#, ActivityDate, Operator

For each App# there are multiple instances of ActivityDate & Operator, but I would like the report to return only the first (minimum) date and operator. Need to be able to summarize (count) instances.

Results should look like this:

Smith,John
App1 10-01-2009
App4 10-02-2009
Total: 2

If Jones,Keith also worked on App1 on 11-15-2010, this record would not be included in the report.

Thanks in advance for the help!!

 

[fisheromacse]

create a group on Operator.
create a formula or SQL expression to find the minimum date per operator.
place the formula or SQL expression in the group footer.

to determine the count, i think more info is needed. what makes a value need to be counted? (or not counted?)

 

[MollyH]

The value that needs to be counted, or what I've been counting, is a distinct count of App#s. Or the number of households served during that time period, grouped by counselor.

Basically, the report is showing that a household (app#), received services (ActivityDate) during a certain time period, and that counselor (Smith,John) entered the activity.

For each counselor, I need a count of households served. Since multiple counselors can enter activity on that app, I have been instructed to only count the earliest instance of activity/counselor.

Hope that helps clarify a bit.

 

[lbass]

Create a SQL expression {%mindate} like this:

(
select min(`ActivityDate`)
from table A
where A.`App#` = table.`App#`
)

Then in the record selection formula, use {table.ActivityDate} = {%mindate}.

There will then be one record per App# and you can group on operator. You mention a certain date range, and you would need to build that into the expression also, using:

A.`ActivityDate` >= //etc.

You cannot use parameters in SQL expressions though, so you would have to hard code the range. Or you could do something similar in a command where you can use parameters.

-LB