I had a quick look but it's impossible to understand what you are trying to do without some sample data.
The problem is with
WHERE eob2.trans_id = eob.trans_id
and with
and (trans_id = eob.trans_id or trans_id is null
and header_trans_id = eob.header_trans_id)
Referring to the outer query within the sub-query creates what is known as a "correlated sub-query" and they spell doom for performance (see why at
SQL server performance - Death by correlated subqueries - SQL Service[
^] )
You were going along the right track by trying to create a temporary table for the sub-queries (nb plural! There is more than one) and you should persist in that direction.
Generate a table that contains your stuffed text for all trans ids and header_trans_ids e.g.
insert into #demo
select trans_id, header_trans_id, replace(STUFF((
SELECT '\par '+ record_text FROM era_eob_storage
WHERE file_archive_id IN
(
SELECT t2.file_archive_id FROM transactions t2
inner join @era_eob_storage eob2 on eob2.trans_id = t2.trans_id
WHERE t2.person_id = '00A2A878-1C27-4453-B7E8-66093B11D510'
)
ORDER BY record_type, seq_nbr
FOR XML PATH('')
),1,5,''),char(13)+char(10),'\par ')
from era_eob_storage
You could even do this for every person_id - it depends on the volume of data you are handling. Do something similar for the other sub-query. Then your query becomes something like the following (bear in mind I have not been able to test this!!)
select
pe.person_id as patientid,
t.trans_id as noteid,
convert(varchar(50),pe.enc_nbr) +'_'+ convert(varchar(50),PE.practice_id) as ticketnumber,
pe.enc_id as encounterid,
pe.practice_id as practiceid,
isnull(d.stuffed,d2.stuffed) as practice
from patient_encounter pe
left join transactions t on t.source_id=pe.enc_id and t.practice_id = pe.practice_id and t.source_type = 'v'
inner join era_eob_storage eob on eob.trans_id = t.trans_id
left outer join #demo d on d.trans_id = eob.trans_id and d.header_trans_id = eob.header_trans_id
left outer join #demo2 d2 on d2.trans_id = eob.trans_id and d2.header_trans_id = eob.header_trans_id
where pe.person_id = '00A2A878-1C27-4453-B7E8-66093B11D510'
and t.era_post_ind='Y'
and convert(varchar(50),pe.enc_nbr) +'_'+ convert(varchar(50),PE.practice_id)='11454322_0001'
That final part of your
where
clause won't be helping either. Instead of
and convert(varchar(50),pe.enc_nbr) +'_'+ convert(varchar(50),PE.practice_id)='11454322_0001'
use
and pe.enc_nbr = 11454322 and practice_id=1
If Yyou use the "Improve Question" link to provide some sample data for each of the tables used, and also the results you would expect to see for that sample data, I might have a chance of helping you further.