Getting data to populate based on other formula` 1

[crachic]

I am using Crystal XI.

I have two tables,
Table 1: Admit table that give me which type of admit it is, either pre-admit or admit
Table 2: Episode totals that gives me the episode balance for all episodes that have a balance

My report is supposed to give the following output.
# patients Balance
21 $3,425.34

I know that I have 21 episodes that have a admit status of Pre-Admit. I also know that of those 21 episodes, 15 of them have a balance. Currently the way I have my two tables setup I am getting the following output:
# patients Balance
15 $3,425.34

The reason this is happening is because of the 6 episodes that do not have a balance, they are not in the episode totals table.

I am trying to get the total number of episodes that have a status of Pre-admit (no matter if they have a balance or not) and the overall balance of these episodes.

Any ideas?

-crachic

 

[lbass]

You need a left join FROM the admit table to the episodes table, with no selection criteria on the second table.

However, if you need to select on a field in the second table, you could use a command as your datasource and add the criterion in the from clause of the command (instead of the where clause). This would allow the left join to be maintained as in:

Select Admit.`PatientID`, Episode.`Balance`
from Admit left outer join Episode on
(
Admit.`PatientID` = Episode.`PatientID` and
Episode.`Date` >= {?StartDate} and
Episode.`Date` < {?EndDate}+1
)

...where you create the date parms in the command screen.

-LB

 

[crachic]

I did the left outer join but I do need to select a field from the episode table. I do not understand where I need to input the criterion that you gave me at. Can you pleae calrify. I apprecaite it.

-crachic

 

[lbass]

In the database expert above the list of tables, you should see "add command"--if you click on that, you should be able to enter a query directly.

If you'd like help, please go to database->show SQL query and copy and paste the current SQL (with the selection criteria appearing) directly into this thread.

-LB

 

[crachic]

Here you go.


SELECT view_episode_summary_admit.EPISODE_NUMBER, view_episode_summary_admit.program_X_type_code, view_episode_summary_admit.preadmit_admission_date, billing_episode_totals.episode_balance
FROM SYSTEM.view_episode_summary_admit view_episode_summary_admit LEFT OUTER JOIN SYSTEM.billing_episode_totals billing_episode_totals ON view_episode_summary_admit.PATID=billing_episode_totals.PATID

 

[lbass]

You didn't show the selection criteria though. To get the SQL for us to work with, go to report->selection formula->record and add your desired criteria there, and then copy the "show SQL query" into the thread. Or at least copy the record selection formula into the thread, if it doesn't pass to the SQL.

-LB

 

[crachic]

I think this is what you are looking for.


SELECT view_episode_summary_admit.EPISODE_NUMBER, view_episode_summary_admit.program_X_type_code, view_episode_summary_admit.preadmit_admission_date, billing_episode_totals.episode_balance
FROM SYSTEM.view_episode_summary_admit view_episode_summary_admit LEFT OUTER JOIN SYSTEM.billing_episode_totals billing_episode_totals ON view_episode_summary_admit.PATID=billing_episode_totals.PATID
WHERE view_episode_summary_admit.program_X_type_code='P' AND view_episode_summary_admit.preadmit_admission_date<={d '2008-03-20'}

 

[lbass]

Your selection criteria are on the left hand table, so you don't have to do anything special to maintain the left outer join. Run the report as is and you should get the correct results.

-LB