I have a Duplicate issue or obtaining the first record

[Sahubba]

Hi everyone,
I have a report that is Grouped by

Location

Prov Name

Patient Id.

The issue that I am running into is that I need a total count for the Patient Id for every Prov Name and a overall total amount in the location. What is happening is that I am getting duplicates in Group 2 (Prov Name) because it is not looking at the first Prov Name that was assign to that patient. Example

Prov Name: Bill Moir Total:1
Patient Id: 15556 Time Recorded: 11:04:22

Prov Name: Sally greo Total:1
Patient Id: 15556 Time Recorded: 11:04:48

So, the location total amount is 1

and the Prov Name all together has a total of 2 when it should be only 1.


Please Help!
Thank you

 

[Madawc]

Instead of a count, do a Distinct Count, which would only count 1 in the case you show.

The use of Crystal's automated totals is outlined at FAQ767-6524.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[Sahubba]

Hello Madawc,

Thanks for your reply but it is already set as a distinct count.

 

[lbass]

But the patient actually saw each of those providers, so the providers are correct per provider--the lower count per location reflects that overlap. So are you saying you want the report to show the customers only under their first provider contact?

-LB

 

[Sahubba]

LB-
Yes that is what I am saying.
I only need the first contact with the provider.

 

[lbass]

Create a SQL expression {%first}:

(
select min(`timerecorded`)
from table A
where A.`PatientID` = table.`PatientID`
)

Then in the record selection formula use:

{table.patientID} = {%first}

If you are having trouble with the SQL expression, please identify your datasource and the version of CR you are using, as these affect the way you set up the expression.

-LB

 

[Sahubba]

Hello LB,

It didn't work.
I know why it did not work, I need to use 2 other feilds to determine if that provider was assigned to that customer.
Along with that feild it defaults to the last provider that was assign and not the first one.

and when the SQL by just using the Patient Id it is giving me no data back.

And I am using Crystal 2008.

Thank you,

 

[lbass]

You haven't provided enough information for me to assist. Please explain what you mean. If you want the earliest contact per patient ID, other fields don't come into the mix, unless the date field and the patient ID are in different tables, or if you have other selection criteria that are relevant.

What is your database? It might help to see the database->"Show SQL query", so please paste that into the thread.

Also show what you tried for the SQL expression.

-LB

 

[Sahubba]

Hello LB,
There is another Table that I am using to get the first assigned Prov.

I have 2 tables pat id and event
in the event table I am using 2 columns, Event time and Assign Prov.

I can paste the Sql but it is long and messy.


Let me know if you want it.

Thanks

 

[lbass]

Please show what you tried for the SQL expression that didn't work and you have not explained how the other two fields relate to the patient ID. Is there a patientID field in the Events table?
How is the Events table linked to the Patient table? On what field?

-LB

 

[Sahubba]

Hello, LB
I ended up using this Sql statement
row_number() over (partition by "PAT_ENC_HSP"."pat_enc_csn_id",
"ED_IEV_EVENT_INFO_END"."event_display_name"
order by "ED_IEV_EVENT_INFO_END"."event_time") as rownum

and then I did a record selection
{@Sum of Total} = 1.00

and it worked