I have a report that displays Subscriber numbers and claim numbers filtered down to five groups. There are anywhere from 1 to 10+ claim numbers per subscriber number. Some of these claim numbers are completely different while others only differ in the last to digits (xxxxxxxx00, xxxxxxxx01, etc). I need to filter the claim numbers as such:
If there are multiple claim numbers contained within a subscriber number, the claim numbers that are completely different need to be returned (xyxyyyxy00, yxyyxxyx00, etc) while the claim numbers that only differ in the last two digits filter to only the highest instance.
Example 1:
Sbr No = 123321
Clm No contained in db = yxyxyxyx00, xyxyxyxy00, yyxxxxyx00, xxyyyxxx00
The above should filter as is since they are all completely different.
Example 2:
Sbr No = 321123
Clm No in db = xxxxxxxx00, xxxxxxxx01, xxxxxxxx03
The above should filter to only show xxxxxxxx03.
I have tried the following as my filter: maximum(distinct[Claim Number] for [Subscriber Number]) but this only gives me the maximum for each Sbr No regardless of how the claim numbers look (in Example 1 above, the filter returns only xxyyyxxx00 instead of all claim numbers).
Does anyone have any ideas on how I can accomplish this?
just a suggestion:
You could try using Substring or SUBSTR depending on what your database is (SQL server or Oracle) to pick the first 6 characters. If you do a distinct of that, then for each value selected, you display the max value just like you are doing now.
PC