Filtering Out Dups

[tmozer]

In one of the main Oracle tables I am working with, each record indicates a completed assignment. However, what constitutes a "case" is a case number assigned to a unit at any given time. In-other-words there might be two assignments to the same unit from a single case. This would be two records/two assignments but still only count as one case.

I can do a distinct count of unique case id fields to summarize a lot of the values I need to collect, but I am not sure how to filter out more then one unit assignment per case to do math type summaries, such as turn-around-time for a date range (average TAT for past 30 and 90 days). I need to ignore the dup unit assignments within the desired date range and then do the math.

How might I ignore the dups while evaluating the records that fall within the date range??

 

[synapsevampire]

The following helps to clarify requirements:

Crystal version
Database/connectivity used
Example data
Expected output

It may be that these aren't duplicate rows, rather record inflation, but to get the number of rows with a unique combination of elements, try creating a formula such as:

{table.field1}+"-"+{table.field2}

Then do a distinct count of those.

Hard to show what you want from this posting, hopefully this will help.

You might use a Running Total to qualify the date ranges, by coding it into the Evaluate->Use a formula, such as:

datediff("d",{table.date}, currentdate) in 30 to 60

-k

-k