Selection

[crystal2770]

I need to select specific data records from the database but cant seem to figure out the formula...

My current selection looks like this..
{TPB110_PMT_ADJ_DETAIL.pmt_pst_ts}
in DateTime (2007, 01, 01, 00, 01, 00)
to DateTime (2007, 12, 31, 23, 59, 00) and
{TPM311_VISIT_PAYOR.pyr_seq_no} = 4981 and
{TPB900_CHG_CODE_MST.chg_cod_ext_id} in [ "91000", "91001", "91002", "91003", "91004", "91005", "91006","00002","90435" ,"90285", "00119", "00309", "00325", "00614", "00630", "00648","00135"]

However, what I need is that for each group of records, that it has at least a "9" code and any one of the others..

Thanks in advance...

 

[highsmith]

begins with a 9 or just has a 9 in the code?
if you want any other one that begins with a 9 use the following:

...and
(
{TPB900_CHG_CODE_MST.chg_cod_ext_id} in [ "91000", "91001", "91002", "91003", "91004", "91005", "91006","00002","90435" ,"90285", "00119", "00309", "00325", "00614", "00630", "00648","00135"] 

or

{TPB900_CHG_CODE_MST.chg_cod_ext_id} like "9*"
)
[/[code]

hope this helps

cheers 
--
See and search my pics at [url=http://www.willhighsmith.com]willhighsmith.com[/url]

 

[crystal2770]

No, Im sorry, what I mean is that when it retreives the records, that if the group has 5 records one must have one of the 9 series codes from my selection..

What I need..
Group1 Name Code $
00001 Test 91000 10.00
00001 Test 00002 10.00
00001 Test 00309 11.00
00001 Test 00309 15.00 ect..

Not what I need is what my select criteria is giving is anything in my IN (this group is not needed because at least one of the records should be in the 9 series above..
Group1 Name Code $
00001 Test 00614 10.00
00001 Test 00002 10.00
00001 Test 00309 11.00
00001 Test 00309 15.00 ect..

 

[highsmith]

Are you using the database expert to get your data or did you write a command statement? What database are you using?

With such a detailed selection formula I'm guessing you're using the database expert.

I would normally accomplish this through my command statement by writing a where statement with a TSQL subquery. Example:

select x
from y
where z
and group in
--make a list of groups that have a code of 9*
(
select group
from y
where code like '9%'
)

It's not possible to my knowledge to do a subquery with the database expert, but there could be a way.

You might could try grouping by your group field, then make a summary to select the max code for each group.
Then you could suppress any group that didn't begin with a 9.

I don't have time to type all that out for you but it's an idea... You just need to check to make sure the suppressed groups aren't calculating in your totals.



cheers
--
See and search my pics at willhighsmith.com

 

[lbass]

Create a formula {@has9} in the field explorer like this:

if {table.code}[1] = '9' then 1

Then go to report->selection formula->GROUP and enter:

sum({@has9},{table.group}) > 0

-LB