|
joebuzz83
Starting Member
16 Posts |
 Posted - 2012-01-09 : 10:18:14
|
| Hi all,I am in situation where I need to report on multiple tables. One of the tables contains duplicate values purposely stored in a column, but the report only needs to show one of those values. I read through this post([url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=140382[/url]), but still need help setting up the solution within my query. My query joins 8-10 tables and pulls data from almost all of them, which gets really confusing to someone who isn't used to complex report writing. Here is the query (followed by sample results): (Bold underlined text shows my notes to you)Select p.last_name +', ' + p.first_name as 'Consumer Name', ((case when p.address_line_1 is null then '' else p.address_line_1 end )+' '+(case when p.address_line_2 IS NULL then '' else p.address_line_2 end )+' '+(case when p.city is null then '' else p.city end)+', '+(case when p.state is null then '' else p.state end)+' '+(case when p.zip is null then '' else p.zip end)) as 'Address', pp.policy_nbr as 'Insurance ID'--2, pp.payer_name as 'Carrier Name'--3,pm.last_name + ', ' + pm.first_name as 'Rend. Provider'--4,pe.enc_nbr as 'Encounter #'--5,(Case when sim.cpt4_code_id > '5000' then sim.cpt4_code_id else '' end) as 'CPT'--6,(Case when sim.cpt4_code_id between '0000' and '5000' then sim.cpt4_code_id else '' end) as 'APG'--7, sim.description as 'Description' , Convert(varchar,pe.create_timestamp,101) as 'Date of Service'--8, Convert(varchar,convert(datetime,c.begin_date_of_service),101) as 'First Bill Date'--9, Convert(varchar,convert(datetime,c.end_date_of_service),101) as 'Last Bill Date'--10,(select DATEDIFF(DAY, pe.create_timestamp, GETDATE())) as 'Days from Service Date'--12, (datetime,t.tran_date),101), GETDATE()))))) else '' end) as 'Days from Denial Date'--13, (case when t.reason_codes_display_only!='' then DATEDIFF(dd,DATEADD(dd,-16,t.tran_date),GETDATE()) else '' end) as 'Days from Denial Date', t.billed_amt as 'Billed Amount'--14 (This is the column I need to limit to only one value of duplicated data. Transactions table. The value should only show if sim.cpt4_code_id like '90%'), (case when t.reason_codes_display_only!='' /*and c.pat_amt!='0'*/ then cast(t.billed_amt as varchar) else '' end) as 'Denied Amount', (case when td.paid_amt is null then '0' else td.paid_amt end) as 'Payment'--15, td.adj_amt as 'Adjustment', c.pat_amt as 'Balance'--16, tcm.description as 'Description', (case when t.reason_codes_display_only!='' then Convert(varchar,convert(datetime,t.tran_date),101)else '' end) as 'Check Date'--17, (case when t.reason_codes_display_only='' then 'Paid' when t.reason_codes_display_only is null then '' else t.reason_codes_display_only end) as 'Denial Reason Code'--20, Convert(varchar,convert(datetime,t.tran_date),101) as 'Denial Date'--21, t.tracking_desc_40 as 'Remittance/Check Number'--22, (case when t.reason_codes_display_only!='' then dateadd(dd, -16, (Convert(varchar,convert(datetime,t.tran_date),101))) end) as 'Cycle/Denied Date'--23, pe.enc_status as 'Encounter Status'--24,td.rebill_indfrom patient_encounter pe join person p on pe.person_id = p.person_idjoin charges c on c.source_id = pe.enc_idjoin service_item_mstr sim on sim.service_item_id = c.service_item_id and sim.service_item_lib_id = c.service_item_lib_id and c.begin_date_of_service between eff_date and sim.exp_datejoin provider_mstr pm on pe.rendering_provider_id = pm.provider_idjoin transactions t on t.source_id = c.source_idjoin trans_detail td on td.trans_id = t.trans_id and td.charge_id = c.charge_id and td.source_id = c.source_idjoin tran_code_mstr tcm on tcm.tran_code_id = t.tran_code_idleft join person_payer pp on pp.person_id=t.person_idand t.payer_id=pp.payer_idorder by 1,2,6What shows now: Code------Billed AMT____________________90000------80.0043200------80.0043200------80.00I need it to show :Code------Billed AMT____________________90000------80.0043200------<Blank>43200------<Blank> |
|
show only one record of a duplicate value