I am trying to do a report which shows all payments we have received and for the report I have to show names of patients who pay, but this table also contains checks from payers (insurance companies) and after I do a join all of the payers are excluded. I have tried every join version I know left, right, outer, inner, and combinations of the two. SQL Server 2005.
select
pay.patient_id,
p.lname + ', ' + p.fname as 'Name',
pay.source_type,
pay.instrument,
pay.doc_reference,
pay.instrument_date,
pay.payment_amount,
pay.user_id,
pay.entry_chron,
pay.payor_id
from payment pay
join (select p.*, max(episode_id) over (partition by patient_id) as maxei from patient p) p
on p.patient_id = pay.patient_id
where episode_id = maxei and (pay.instrument_date between '2014-11-01' and '2014-11-30')
order by pay.payment_amount
This is what the results look like for patients with some fields commented out for confidentiality. 
These are the fields that are being excluded 
Aucun commentaire:
Enregistrer un commentaire