SELECT p.id AS 'Patient ID'
	,l.txid AS 'listing TX ID'
	,l.Id AS 'Listing ID'
	,s.Org_Type
	,p.firstname
	,p.lastname
	,zgd.NAME AS 'Doctor Group'
	,p.Phone1
	,p.city + ', ' + p.STATE AS 'City,State'
	,CONVERT(VARCHAR, p.birth_date, 101) AS 'DOB'
	,(
		SELECT [dbo].[getageyears](p.Birth_Date, getdate()) AS patient_age
		) AS 'Age'
	,p.sex AS 'Sex ID'
	,zs.NAME AS 'Sex'
	,(
		SELECT [dbo].[GetPatientHeight](rd.Pa_Id)
		) AS 'Height cm'
	,(
		SELECT [dbo].[GetPatientWeight](rd.pa_id)
		) AS 'Weight kg'
	,cast(rd.weight * 2.205 AS DECIMAL(9, 1)) AS 'Weight lbs'
	,(
		SELECT CONVERT(VARCHAR, l.list_date, 101)
		WHERE l.centerid = 276
		) AS 'Center1 List Date'
	,(
		SELECT CONVERT(VARCHAR, l.list_date, 101)
		WHERE l.centerid = 345
		) AS 'Center2 List Date'
	,(
		SELECT DATEDIFF(day, l.list_date, getdate())
		WHERE l.centerid = 345
		) AS 'Center2 Wait List Days'
	,lhu.UNOScPRA
	,p.BloodType
	,zbt.NAME AS 'Blood Type'
	,l.disposition
	,zl.NAME AS 'Status'
	,l.centerid
	,zltc.NAME AS 'Listing Center'
	,l.remarks AS 'Notes'
FROM Patient p
INNER JOIN TX_Shared_Main s ON p.id = s.pa_id
INNER JOIN TX_REC_Listing l ON l.txid = s.id
INNER JOIN Z_BloodType zbt ON zbt.id = p.BloodType
INNER JOIN TX_Z_Listing_Disposition zl ON zl.id = l.disposition
INNER JOIN TX_Lookup_Transplant_Centers zltc ON zltc.id = l.centerid
INNER JOIN Z_Sex zs ON zs.Id = p.sex
INNER JOIN Rdv_Detail rd ON rd.Pa_Id = p.Id
LEFT JOIN doctor d ON d.id = p.Dr_Id
LEFT JOIN patient_Reference pr ON pr.aux_id = d.id
LEFT JOIN Group_Doctor gd ON gd.dr_id = d.id
LEFT JOIN Group_Z_Doctor zgd ON zgd.id = gd.group_id
LEFT JOIN TX_REC_Histo_UNOScPRA lhu ON lhu.pa_id = p.Id
WHERE p.InUse = 1
	AND l.InUse = 1
	AND s.InUse = 1
	AND p.Birth_Date IS NOT NULL
	AND s.Org_Type = 5
	AND l.disposition = 1