Compare Records in the same table

[Fadius]

I have a result set from a query from a database I have been given. My task is I need to compare records in determine what to display in the report.

Data Example

ProviderID Payor Status
123456 FL Medicaid Participating
123456 FL UHC Pending
123456 FL AMC Participating


In the above Example, FL UHC and FL AMC require Medicaid to be participating before they can be worked. So I would only want the FL UMC record to be displayed as that one needs to get started.

Converseley, if the Medicaid is not in a Participating Status, no records would show up as it is not complete yet.


Thank s in advance.

 

[lbass]

There is no UMC record, so it is unclear what you want the display to be. Also, do you want the Medicaid row to appear if it exists?

-LB

 

[hilfy]

Assuming you've loaded the dataset into a table and you're not working with a Command in crystal, you'll need to use a second "copy" of your table in the report. When you do this, Crystal will automatically let you know that the table is already in the report and will ask you if you want to "alias" it. It will then display the table with the table name + "_1". For the purpose of this example I'll call the table Provider_Payor.

You'll do something like the following:

- Join FROM ProviderID in Provider_Payor TO ProviderID in Provider_Payor_1. Leave this as an Inner Join.

- In the Select Expert, set up the following selection criteria:
{Provider_Payor.Payor} = 'FL Medicaid' and
{Provider_Payor.Status} = 'Participating' and
{Provider_Payor_1.Payor} <> 'FL Medicaid' and
{Provider_Payor_1.Status} <> 'Participating'

You then use the data from Provider_Payor_1 in the report. This will provide information about all provider/payor sets where the provider participates in Medicaid, but does not yet participate with the specified payor (which is what it looks like your requirements are from your post.)

-Dell



A computer only does what you actually told it to do - not what you thought you told it to do.

 

[Fadius]

Thanks . I will give it a try. lbass, I meant UHC, sorry, typo. The table is pull of providers and payors they are linked to that require medicaid to be done first before they can be worked.

I just need to report those providers who have payors that can be started to be worked as the medicaid is done.

 

[lbass]

Group on Provider ID. Create two formulas like this:

//{@hasMedicaid}:
if {table.payor} = "Medicaid" and
{table.status}="Participating" then 1

//{@hasPending}:
if {table.status}="Pending" and
{table.payor}<>"Medicaid" then 1

Then go to report->selection formula->GROUP and enter:
sum({@hasMedicaid},{table.ProviderID})=1 and
sum({@hasPending},{table.ProviderID})>=1

This would return the Providers that have Medicaid and at least one "Pending". If you don't want records within the group to show, you could use the section expert to certain suppress
details, e.g., all those not pending, if you wish.

-LB