Need Formula for most current data

[Bugzvh]

Hi,

I’m relatively new with Cystal Reports and would appreciate any help with a formula. I am using CR 10 and my data is stored in several tables. What I want is a report of individual services per client with diagnosis, location code and amount of service.

For example: This is the data I want.

ClientID Date of Ser Ser Cd Amt Diag Loc Cd
474747 08/01/09 90862 56.00 311 CS40
474747 08/02/09 90808 47.50 311 CS40
474747 08/10/09 90806 35.40 311 CS40
474747 08/30/09 97535 45.00 311 CS40

My problem is for each diagnosis update I get individual services per diagnosis update. It’s overstating the amounts per service per client. Can anyone help with a formula to state that I only want the most recent diagnosis information. I have already used the Select Distinct Records in Crystal but it didn’t help much.

For example: This is what I get.

ClientID Date of Ser Ser Cd Amt Diag Loc Cd
474747 08/01/09 90862 56.00 311 CS40
474747 08/01/09 90862 56.00 312.58 CS40
474747 08/01/09 90862 56.00 313.13 CS40
474747 08/02/09 90808 47.50 311 CS40
474747 08/02/09 90808 47.50 312.58 CS40
474747 08/02/09 90808 47.50 313.13 CS40
474747 08/10/09 90806 35.40 311 CS40
474747 08/10/09 90806 35.40 312.58 CS40
474747 08/10/09 90806 35.40 313.13 CS40
474747 08/30/09 97535 45.00 311 CS40
474747 08/30/09 97535 45.00 312.58 CS40
474747 08/30/09 97535 45.00 313.13 CS40

 

[CoSpringsGuy]

Are you linking the tables by ClientID? Are there other fields in the table that Diag comes from that would allow you to further determine the Diag Codes you want? Like DOS?

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[LoriDurham]

Please Group on Date and Take Max(fields,datefield) and place them in the group footer. You would get the latest recent data.
Thanks,
Lori

 

[CoSpringsGuy]

Loris suggestion will work great unless you are going to need an amount total for each clientID

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[CoSpringsGuy]

you would need to use something like this formula in your record selection group to bring in the data you need:
Use this for the selection criteria to bring in all the information you need
(
{invoice_date} >= {?startdate}
and
{invoice_date} >= {?stopdate}
)
or
(
{invoice_date} >= date(year({?startdate})-1,month({?startdate}),day({?startdate}))
and
{invoice_date} >= date(year({?startdate})-1,month({?startdate}),day({?startdate}))
)

From there you will have to use grouping and suppression to get the invoice to look like what you need. See if that gets you started.

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[CoSpringsGuy]

wrong thread

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[lbass]

Your sample data does not show a field that would indicate which diagnosis is the most recent per service code. Do you have a time field? Or an ID field per updated diagnosis?

You could insert groups on client and then on date and service code. Then insert a running total that sums the amount on change of group: service code. Place the fields in the service code group section and suppress the other sections.

-LB