Patients duplicating - Crystal 10 Technical User

[maruja]

Greetings:

I have been asked to write a report showing all Cancer pts who either had a consult or did not. This is accomplished by inserting a Group. The Group consists of an indicator:
ind_consult_ind: inpatients that have at least one consult provided by a physician count as 1 otherwise 0.
I have set up a formula: if {c_cmb_vst_v_20050303.ind_consult_ind} = 1 and {c_pract_dim_cons_v.pract_no} in "047001", "045773", "045781","110353", "047027", "048983","046508","046441","103382","610782"])then "Oncologist Consulted on Case" else "No Oncology Consult" .
FYI: (c_pract_dim_cons_v.pract_no = physician numbers.)

So the report shows as follows:

No Oncology Consult 247
Oncologist Consulted on Case 63

However, when you drill down on the groups I am finding the same patient in both groups. The patient may have had 5 consults; one of them being done by the doc who is in my formula above. The pt should only be in 'Oncologist Consulted on Case'. Why is the patient appearing in 'No Oncology Consult'?
Here is my SQL:

SELECT DISTINCT "c_cmb_vst_v_20050303"."pt_id", "c_cmb_vst_v_20050303"."pt_name", "c_cmb_vst_v_20050303"."len_of_stay", "c_cmb_vst_v_20050303"."dsch_date", "c_cmb_vst_v_20050303"."drg_glos", "c_cmb_vst_v_20050303"."ind_consult_ind", "c_cmb_vst_v_20050303"."tot_chg_amt", "c_cmb_vst_v_20050303"."exp_reimb", "c_cmb_vst_v_20050303"."orgz_name", "c_cmb_vst_v_20050303"."prim_pyr_cd", "c_cmb_vst_v_20050303"."hosp_svc", "c_cmb_vst_v_20050303"."drg_no", "c_cmb_vst_v_20050303"."vst_type_cd", "c_cmb_vst_v_20050303"."cost_admitting", "c_cmb_vst_v_20050303"."cost_benefits", "c_cmb_vst_v_20050303"."cost_bldgs_fix", "c_cmb_vst_v_20050303"."cost_care_mgmt", "c_cmb_vst_v_20050303"."cost_clin_eng", "c_cmb_vst_v_20050303"."cost_con_labor", "c_cmb_vst_v_20050303"."cost_csr", "c_cmb_vst_v_20050303"."cost_deprec", "c_cmb_vst_v_20050303"."cost_drugs", "c_cmb_vst_v_20050303"."cost_environ", "c_cmb_vst_v_20050303"."cost_food", "c_cmb_vst_v_20050303"."cost_fsalaries", "c_cmb_vst_v_20050303"."cost_gme", "c_cmb_vst_v_20050303"."cost_him", "c_cmb_vst_v_20050303"."cost_malpract", "c_cmb_vst_v_20050303"."cost_med_sup", "c_cmb_vst_v_20050303"."cost_other", "c_cmb_vst_v_20050303"."cost_other_sup", "c_cmb_vst_v_20050303"."cost_plant_op", "c_cmb_vst_v_20050303"."cost_pro_fees", "c_cmb_vst_v_20050303"."cost_purch_svc", "c_cmb_vst_v_20050303"."cost_vsalaries", "c_cmb_vst_v_20050303"."calc_bd", "c_cmb_vst_v_20050303"."calc_ca", "c_cmb_vst_v_20050303"."calc_ga", "c_pract_dim_cons_v"."pract_no"
FROM "SMSPHdssp0r0"."smsdss"."c_pract_dim_cons_v" "c_pract_dim_cons_v" INNER JOIN ("SMSPHdssp0r0"."smsdss"."c_cmb_vst_v_20050303" "c_cmb_vst_v_20050303" INNER JOIN "SMSPHdssp0r0"."smsdss"."c_smd_consult_v" "c_smd_consult_v" ON ("c_cmb_vst_v_20050303"."pt_id"="c_smd_consult_v"."pt_id") AND ("c_cmb_vst_v_20050303"."orgz_cd"="c_smd_consult_v"."orgz_cd")) ON "c_pract_dim_cons_v"."pract_no"="c_smd_consult_v"."smd_consult_dr_no"
WHERE NOT ("c_cmb_vst_v_20050303"."prim_pyr_cd"='j24' OR "c_cmb_vst_v_20050303"."prim_pyr_cd"='k01' OR "c_cmb_vst_v_20050303"."prim_pyr_cd"='n15') AND ("c_cmb_vst_v_20050303"."drg_no"=10 OR "c_cmb_vst_v_20050303"."drg_no"=82 OR "c_cmb_vst_v_20050303"."drg_no"=172 OR "c_cmb_vst_v_20050303"."drg_no"=203 OR "c_cmb_vst_v_20050303"."drg_no"=239 OR "c_cmb_vst_v_20050303"."drg_no"=303 OR "c_cmb_vst_v_20050303"."drg_no"=403 OR "c_cmb_vst_v_20050303"."drg_no"=404 OR "c_cmb_vst_v_20050303"."drg_no"=410 OR "c_cmb_vst_v_20050303"."drg_no"=473 OR "c_cmb_vst_v_20050303"."drg_no"=481 OR "c_cmb_vst_v_20050303"."drg_no"=539) AND "c_cmb_vst_v_20050303"."len_of_stay"<>0 AND ("c_cmb_vst_v_20050303"."dsch_date">={ts '2005-01-01 00:00:00'} AND "c_cmb_vst_v_20050303"."dsch_date"<{ts '2005-12-31 00:00:01'}) AND "c_cmb_vst_v_20050303"."tot_chg_amt"<>0 AND "c_cmb_vst_v_20050303"."vst_type_cd"='i'
ORDER BY "c_cmb_vst_v_20050303"."orgz_name"

FYI: DRG_NO is a means of grouping patients by diagnosis and determines reimbursement.

Thank you. Maruja

 

[synapsevampire]

You might try 2 formulas insread of grouping:

//@consult
if {c_cmb_vst_v_20050303.ind_consult_ind} = 1 and {c_pract_dim_cons_v.pract_no} in "047001", "045773", "045781","110353", "047027", "048983","046508","046441","103382","610782"])then
"c_cmb_vst_v_20050303"."pt_id"
else
0

//@noconsult
if not({c_cmb_vst_v_20050303.ind_consult_ind} = 1 and {c_pract_dim_cons_v.pract_no} in "047001", "045773", "045781","110353", "047027", "048983","046508","046441","103382","610782"])) then
"c_cmb_vst_v_20050303"."pt_id"
else
0

This assumes that your formula is correct.

Now create formulas using summary formulas of:

distinctcount({@consult})-1

distinctcount({@noconsult})-1

Seems OK.

-k