Greetings!
I am a "new" user to Crystal Reports in the sense that I have been dabbling in it for the last several years, just completed a "Basic Training" 3 day online course in December.
I am able to get around okay, but one of our supervisors asked for a very specific report. This report has been generated by the Vendor for the last few years and the supervisor wanted me to design a crystal report to mimic it.
I've spent most of this week trying to figure out how to get the numbers she wants. Basically, the Encounter Table has data (Encounter.enco_id and Encounter.ref_enco_id) referring to two dates. She wants an average of the length of time between the first date and the second date. Since the data is in one table, I need to figure out a way to access it without a tremendous amount of coding/formula building.
I asked the original designer for help, which I greatly appreciated, but I was unable to complete my discussion with him due to a scheduled meeting I couldn't miss. He gave me his SQL coding, but I am struggling with how to convert it to Crystal Reports XI (embedded).
I tried making a formula, but I get a "This field name is unknown" error.
Formula attempt:
WhilePrintingReports;
StringVar ref_enco := "encounter";
{(ref_enco).enco_id} = {encounter.ref_enco_id}:
{encounter.start_date}
I understand the majority of it, but I am struggling with converting the bold line (shown below). Here is the SQL coding:
select prle_listname,
Count(clpr_id) as Total_Admits,
Avg(diff) as Average_Difference
from
(select prle_listname,client.listname, ref_enco.start_date, encounter.start_date as
Admit_Date,
datediff(d, ref_enco.start_date, encounter.start_date) as Diff , client_program.clpr_id
from encounter
inner join episode_info on episode_info.enco_id
= encounter.enco_id
inner join encounter as ref_enco on
ref_enco.enco_id =
encounter.ref_enco_id
inner join client_program
on client_program.clpr_id
= episode_info.admit_clpr_id
inner join program_level on program_level.prle_id
= client_program.prle_id
inner join client on client.client_id = encounter.client_id
--Where ref_enco.start_date between
'1/1/2011' and '3/31/2011'
Where ref_enco.start_date between '1/1/2010' and '12/31/2010'
--order by prle_listname,
client.listname
) tmp
group by prle_listname
order
by prle_listname
Thank you for any help that can be provided.
IP Logged
