-----  Bed Assignment Report --------

SELECT
	p.patient_unique_id P_id,
	CONCAT( p.patient_name, " (", IFNULL( p_cons.contact_details, "--" ), ")" ) P_name,
	admt.admission_no admit_no,
	admt.admission_date admit_dt,
	bd.bed_name,
	i_bd_dtls.occupied_from,
	i_bd_dtls.occupied_to,
	service_rates.rate
FROM
	ipd_admission admt
	INNER JOIN patients p ON admt.patient_id = p.id
	LEFT JOIN contacts p_cons ON p_cons.id = (
	SELECT
		p_cons1.id 
	FROM
		contacts p_cons1 
	WHERE
		p_cons1.data_source_id = p.id 
		AND p_cons1.data_source = "Patient" 
		AND p_cons1.contact_type = "M" 
		AND p_cons1.is_active = "Y" 
	ORDER BY
		p_cons1.id DESC 
		LIMIT 1 
	)
	INNER JOIN ipd_bed_details i_bd_dtls ON admt.id = i_bd_dtls.ipd_admission_id
	INNER JOIN bed bd ON i_bd_dtls.bed_id = bd.id
	JOIN service_rates ON service_rates.id = bd.service_rate_id 
	AND service_rates.service_item_id = bd.service_item_id