Suppress or don't query some records that are similar 1

[fergman]

Hey all, I have a report that I'm trying to suppress some data, but here's the problem. I will have a subscriberName, a SubscriberID an EFFDate and a TermDate. The subsc will have 2 records sometimes, one that says effdate is 01/01/2006 and term date is 01/01/2006 and the following row shows effdate 02/01/2006 and 12/31/9999 for termdate (basically means they are active still).

In laymans terms this means they termed off the plan on 01/01/2006 and returned 02/01/2006. The problem results from the fact that I need to know both of those facts and display them, but doing so means I get all 12/31/9999 people (even if they haven't termed). Could I use a whileprintingrecords statement or something to filter those?

 

[lbass]

It is unclear what exactly you want displayed. You need to explain under what circumstances you want the 12/31/9999 people displayed.

-LB

 

[fergman]

I'm sorry, my bad....

I want to see the 12/31/9999 only when the other row exists. For example, I want to see:

subscname subscid effdate termdate
Jane Doe 1112222 1/1/2006 1/1/2006
Jane Doe 1112222 2/1/2006 12/31/9999 <- Want to see

Janet Doe 1112233 2/2/2006 12/31/9999 <- Don't want to
Janice Doe 1113321 2/5/2006 12/31/9999 <- don't want to

Basically Jane termed, but singed back up a month later, where as the other 2 have never termed.

Thanks for the help...

 

[lbass]

Go to report->selection formula->GROUP and enter:

count({table.subscid},{table.subscid}) >= 2

...assuming you have a group on subscid. If you also ONLY want to see the groups with two records when the most recent is active, then change the group selection formula to:

count({table.subscid},{table.subscid}) >= 2 and
maximum({table.effdate},{table.subscid}) = date(9999,12,31)

-LB