Duplicate records in Crystal 8.5

[maruja]

Greetings:

I am writing a report for a cancer department. Basically, they want to see a patient list for DRG 82. This is a diagnosis related group (DRG) which is used in reimubursement and is based on several factors; one being the final diagnosis. There are 4 hospital involved. They also want to see particular activity codes 'actv_cd'(these are the charges that are posted to the patient's record. In this case they are xray charges). My problem is that in some instances the xray charges are duplicating when I know for a fact the patient only had one xray. Here is my SQL:
SELECT DISTINCT
c_cmb_vst_v_20050303."adm_date",
c_cmb_vst_v_20050303."drg_no",
c_cmb_vst_v_20050303."drg_serv_line",
c_cmb_vst_v_20050303."dsch_date",
c_cmb_vst_v_20050303."hosp_svc",
c_cmb_vst_v_20050303."len_of_stay",
c_cmb_vst_v_20050303."med_rec_no",
c_cmb_vst_v_20050303."orgz_short_name",
c_cmb_vst_v_20050303."prim_pract_no_cd_desc",
c_cmb_vst_v_20050303."prim_pyr_cd",
c_cmb_vst_v_20050303."pt_id",
c_cmb_vst_v_20050303."pt_name",
c_cmb_vst_v_20050303."tot_chg_amt",
c_cmb_actv_v_20050303."actv_tot_qty",
c_cmb_actv_v_20050303."actv_cd",
c_cmb_actv_v_20050303."actv_cd_desc",
c_cmb_actv_v_20050303."vst_type_cd",
c_pract_dim_cons_v."pract_name",
c_pract_dim_cons_v."spclty_desc"
FROM
((SMSPHdssp0r0.smsdss.c_cmb_vst_v_20050303 c_cmb_vst_v_20050303 INNER JOIN SMSPHdssp0r0.smsdss.c_cmb_actv_v_20050303 c_cmb_actv_v_20050303 ON
c_cmb_vst_v_20050303."orgz_cd" = c_cmb_actv_v_20050303."orgz_cd" AND
c_cmb_vst_v_20050303."vst_key" = c_cmb_actv_v_20050303."vst_key")
INNER JOIN SMSPHdssp0r0.smsdss.c_smd_consult_v c_smd_consult_v ON
c_cmb_vst_v_20050303."orgz_cd" = c_smd_consult_v."orgz_cd" AND
c_cmb_vst_v_20050303."pt_id" = c_smd_consult_v."pt_id")
INNER JOIN SMSPHdssp0r0.smsdss.c_pract_dim_cons_v c_pract_dim_cons_v ON
c_smd_consult_v."smd_consult_dr_no" = c_pract_dim_cons_v."pract_no"
WHERE
c_cmb_vst_v_20050303."drg_serv_line" <> 'ungroupable' AND
c_cmb_vst_v_20050303."drg_serv_line" <> 'rehab' AND
c_cmb_vst_v_20050303."drg_serv_line" <> 'behavioral health' AND
c_cmb_vst_v_20050303."hosp_svc" <> 'hsp' AND
c_cmb_vst_v_20050303."len_of_stay" <> 0. AND
c_cmb_vst_v_20050303."tot_chg_amt" <> 0. AND
c_cmb_vst_v_20050303."prim_pyr_cd" <> 'j24' AND
c_cmb_vst_v_20050303."prim_pyr_cd" <> 'k01' AND
c_cmb_vst_v_20050303."prim_pyr_cd" <> 'n15' AND
c_cmb_actv_v_20050303."vst_type_cd" = 'I' AND
c_cmb_vst_v_20050303."drg_no" = 82 AND
(c_cmb_actv_v_20050303."actv_cd" = '21701057' OR
c_cmb_actv_v_20050303."actv_cd" = '21700786' OR
c_cmb_actv_v_20050303."actv_cd" = '21700562' OR
c_cmb_actv_v_20050303."actv_cd" = '21700067' OR
c_cmb_actv_v_20050303."actv_cd" = '21700059' OR
c_cmb_actv_v_20050303."actv_cd" = '20431599' OR
c_cmb_actv_v_20050303."actv_cd" = '20431375' OR
c_cmb_actv_v_20050303."actv_cd" = '20431367' OR
c_cmb_actv_v_20050303."actv_cd" = '20431359' OR
c_cmb_actv_v_20050303."actv_cd" = '20431250' OR
c_cmb_actv_v_20050303."actv_cd" = '20431243' OR
c_cmb_actv_v_20050303."actv_cd" = '20431235' OR
c_cmb_actv_v_20050303."actv_cd" = '20431227' OR
c_cmb_actv_v_20050303."actv_cd" = '20431219' OR
c_cmb_actv_v_20050303."actv_cd" = '20431177' OR
c_cmb_actv_v_20050303."actv_cd" = '20431169' OR
c_cmb_actv_v_20050303."actv_cd" = '20431094' OR
c_cmb_actv_v_20050303."actv_cd" = '20431086' OR
c_cmb_actv_v_20050303."actv_cd" = '20431078' OR
c_cmb_actv_v_20050303."actv_cd" = '20431037' OR
c_cmb_actv_v_20050303."actv_cd" = '20431029' OR
c_cmb_actv_v_20050303."actv_cd" = '20431011') AND
c_pract_dim_cons_v."spclty_desc" = 'oncology'
ORDER BY
c_cmb_vst_v_20050303."orgz_short_name" ASC,
c_cmb_vst_v_20050303."pt_id" ASC

Can you help me please? Maruja

 

[synapsevampire]

This is the SQL from the report, or?

Anway, what you're experiencing is commonly termed record or row inflation.

Insert a group by the actv_cd and place your fields in there, and suppress the details section, or figure out how to properly return the data at the right granularity.

If you've a knowledgable dba for that database they she/he should be able to work it out.

-k

 

[Madawc]

As synapsevampire says, grouping would do it. But what have you got in your record selection? (Report > Selection Formulas > Record).

If it's anything like the SQL, I'd rewrite the tests as 'boolians', formula fields of the form

c_cmb_vst_v_20050303."drg_serv_line" in ["ungroupable", "rehab", "behavioral health"]

. If this were called @UnwantedLine, then the selection should say not @UnwantedLine. And so forth for all of the tests, much neater and safer than a mix of ADD and OR statements

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

 

[maruja]

synapsevampire:

Your suggestion to use 'group by' worked. My question is according to all the documentation I read using 'select distinct' would eliminate duplicate records. Can you take a moment to explain why this did not work and what is record or row inflation?? Thanks so much. Maruja

 

[ZazaFazaza]

Dear All,

I have a Question:

I am using Crystal Reports XI, and I made a Dynamic Paremeter, the problem is that; I need to add "ALL" in the paremeter values

Thanks a lot for your cooperation.

Best Regards,
ZazaFazaza