I want to return records where there is no service plan in place

[mirror152]

I'm working with CR11 and SQL server DB.

I'm doing a report that is supposed to tell me if all of the elements of a treatment plan are in place. The command line has lots of LEFT INNER JOINS because a treatment plan is scattered over several tables.

There may be more than one plan in the database. I only want to return the most recent plan.

I also want to return a line when a client has no treatment plan, which is why I'm using the view_episode_summary_current table. This is where I've run into trouble. I've tried using the command line below, but its only returning records for clients who have a plan on file. Thoughts?

Here is the command:

SELECT view_episode_summary_current.PATID,
cw_mh_treatment_plan_input.plan_start_date, cw_mh_treatment_plan_input.last_date_plan_updat, cw_mh_treatment_plan_input.strengths, cw_mh_tp_input_interventions.responsible_staff_name, cw_mh_tp_input_interventions.selected_interventio, cw_mh_tp_input_objectives.objective_status_value
FROM (((SYSTEM.view_episode_summary_current view_episode_summary_current LEFT OUTER JOIN CWSAVPMLIVESYSTEM.cw_mh_treatment_plan_input cw_mh_treatment_plan_input ON (view_episode_summary_current.PATID = cw_mh_treatment_plan_input.PATID) LEFT OUTER JOIN CWSAVPMLIVESYSTEM.cw_mh_tp_input_problems cw_mh_tp_input_problems ON ((cw_mh_treatment_plan_input.FACILITY=cw_mh_tp_input_problems.FACILITY) AND (cw_mh_treatment_plan_input.PATID=cw_mh_tp_input_problems.PATID)) AND (cw_mh_treatment_plan_input.MHHuniqueid=cw_mh_tp_input_problems.MHHuniqueid)) LEFT OUTER JOIN CWSAVPMLIVESYSTEM.cw_mh_tp_input_goals cw_mh_tp_input_goals ON ((cw_mh_tp_input_problems.FACILITY=cw_mh_tp_input_goals.FACILITY) AND (cw_mh_tp_input_problems.MHPuniqueid=cw_mh_tp_input_goals.MHPuniqueid)) AND (cw_mh_tp_input_problems.PATID=cw_mh_tp_input_goals.PATID)) LEFT OUTER JOIN CWSAVPMLIVESYSTEM.cw_mh_tp_input_objectives cw_mh_tp_input_objectives ON ((cw_mh_tp_input_goals.FACILITY=cw_mh_tp_input_objectives.FACILITY) AND (cw_mh_tp_input_goals.PATID=cw_mh_tp_input_objectives.PATID)) AND (cw_mh_tp_input_goals.MHGuniqueid=cw_mh_tp_input_objectives.MHGuniqueid)) LEFT OUTER JOIN CWSAVPMLIVESYSTEM.cw_mh_tp_input_interventions cw_mh_tp_input_interventions ON ((cw_mh_tp_input_objectives.FACILITY=cw_mh_tp_input_interventions.FACILITY) AND (cw_mh_tp_input_objectives.PATID=cw_mh_tp_input_interventions.PATID)) AND (cw_mh_tp_input_objectives.MHOuniqueid=cw_mh_tp_input_interventions.MHOuniqueid)
WHERE cw_mh_treatment_plan_input.plan_start_date = (SELECT MAX(A.plan_start_date) FROM CWSAVPMLIVESYSTEM.cw_mh_treatment_plan_input A, SYSTEM.episode_history B WHERE (A.PATID = cw_mh_treatment_plan_input.PATID) AND (A.patid = B.Patid)) AND
view_episode_summary_current.program_X_RRG_code = {?RRG}
AND NOT (cw_mh_tp_input_objectives.objective_status_value='Achieved' OR cw_mh_tp_input_objectives.objective_status_value='Discontinued')

 

[Madawc]

I think a LEFT OUTER will fail when you have a selection on the second table. You might need to select unwanted details and then use Suppress on detail lines to prevent them being shown.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[mirror152]

Wouldn't I want to use a LEFT OUTER JOIN though because I want the query to return a line when there is no record of a treatment plan (and, of course, when there is as well). If I were to use an inner join, wouldn't it only return a record where there is a treatment plan in place?

The command line that I've used above does run, but it only returns a record when there is a treatment plan in place.

 

[kenhamady]

You would need an outer join but you would also have to remove any filters on the outer table. Filtering on the outer table essentially turns your outer join back into an inner join, because the filter is applied after the join.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/