reference data from one row in another...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

reference data from one row in another...

Author

Topic

joebuzz83
Starting Member

16 Posts

Posted - 2012-01-10 : 15:24:04

Hi all,

How can I reference data from row 2 to conditionally change data in row 1?

If my table shows this type of data:

code-----description----Status-----Value
90001----TX 1-----------Billed-----$70
90002----TX 2-----------Billed-----$70
90002----TX 2-----------Voided-----$70

I need to show this:
code-----description----Status-----Value
90001----TX 1-----------Billed-----$70
90002----TX 2-----------Billed-----$0
90002----TX 2-----------Voided-----$0

So if at some point in time, if the second code was marked as Voided I need to show that there is no Value attached to the row.

My query looks like this so far, so please bear that in mind when answering.

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
,(Convert(varchar,convert(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'
, (CASE WHEN sim.cpt4_code_id like '90%' THEN t.billed_amt ELSE 0 END) as 'Billed Amount'--14
, (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'
, Convert(varchar,convert(datetime,t.tran_date),101) 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_ind
from patient_encounter pe
join person p on pe.person_id = p.person_id
join charges c on c.source_id = pe.enc_id
join 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_date
join provider_mstr pm on pe.rendering_provider_id = pm.provider_id
join transactions t on t.source_id = c.source_id
join trans_detail td on td.trans_id = t.trans_id and td.charge_id = c.charge_id and td.source_id = c.source_id
join tran_code_mstr tcm on tcm.tran_code_id = t.tran_code_id
left join person_payer pp on pp.person_id=t.person_id
and t.payer_id=pp.payer_id

joebuzz83
Starting Member

16 Posts

Posted - 2012-01-10 : 16:27:22

can someone please tell me if this is at all possible? I am wasteing a lot of time trying to figure this out. Perhaps I wasn't clear enough?

If I have 5 rows that contain the same value:

1. 500
2. 500
3.500
4. 500
5. 500

I would like to make the data show only one of those values:

if code=90000 then 500

The problem is that there may be three rows with 90000 as the code and that logic will add 500 to all three rows.

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-10 : 16:31:08

SELECT DISTINCT Code, Value?

I'm not following you

Let's get Simple

Sample Data

9000 500
9000 500
9000 500
9001 600
9002 700
9002 800

So what do you want to see out of that?

9000 500
9001 600
9002 ???

????

Do that

Post sample data and what you expect to see as the final result

That query is massive

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

joebuzz83
Starting Member

16 Posts

Posted - 2012-01-10 : 16:44:28

Hi Bret,

My sample code is below. Notice in the first table, code shows 90002 twice and 90001 once. All rows have a Value of $70. My accounting department only wants to see one of those $70 Values.

code-----description----Status-----Value
90001----TX 1-----------Billed-----$70
90002----TX 2-----------Billed-----$70
90002----TX 2-----------Voided-----$70

So, since I only need one of those values, I'll choose the row with code 90001 to continue to hold the $70 value because both rows contianing 90002 were not valid in my system and the orders were canceled. Unfortunately, in this database, there isn't an indicator stating that the order was cancelled on each row.

Continuing on, Notice that two of the rows show a status of billed. Since that is th case I want to show only one of the two with the $70.

I need to show this:
code-----description----Status-----Value
90001----TX 1-----------Billed-----$70
90002----TX 2-----------Billed-----$0
90002----TX 2-----------Voided-----$0

I hope this helps to clarify, although after reading the scenario again, its a bit more confusing than I thought.

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-10 : 16:54:09

but what if there is a 90003 with a billed $90
and a 9004 with a billed of $100, then a a 9004 with a void of $100

What then?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

joebuzz83
Starting Member

16 Posts

Posted - 2012-01-10 : 17:10:01

Good point. The scerio changes to this:

If there are multiple codes that show up in the table that do not have two rows with with at least one Status of Voided or Cancelled then:

code-----description----Status-----Value
90001----TX 1-----------Billed-----$70
90002----TX 2-----------Billed-----$0
90002----TX 2-----------Voided-----$0
90003----Tx 8-----------Billed-----$100
90004----Tx 9-----------Billed-----$0
90004----Tx 9-----------Voided-----$0

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-11 : 09:37:28

So you want the values summed up, and placed on every detail line?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources