Select 
FacilityID,
RenAct.uni_int_unitid, 
RenAct.uni_vch_unitno, 
RenAct.uni_int_status, 
RenAct.uty_int_Id, 
RenAct.futr_mny_MonthlyRent, 
RenAct.tul_dtm_moveindate, 
RenAct.tul_dtm_moveoutdate,
RenAct.tul_bit_transferin, 
RenAct.tur_mny_NewRent,
RenAct.tul_int_Leaseno,
RenAct.uni_dtm_DateCreated,
RenAct.futr_dtm_EffectiveDate
From( 
	SELECT 
	fse_int_FacilityId as FacilityID,
	stf_vch_Name as FacilityName,
	units.uni_int_unitid, 
	units.uni_vch_unitno,
	units.uni_int_status, 
	units.uni_bit_deleteflag, 
	unittypes.uty_int_Id, 
	unittypes.uty_vch_code,
	unittypes.uty_int_length,
	unittypes.uty_int_width, 
	unittypes.uty_bit_deleteflag, 
	unittyperent.futr_mny_MonthlyRent, 
	tenantunitleasedetails.tul_dtm_moveindate, 
	tenantunitleasedetails.tul_dtm_moveoutdate,      
	tenantunitleasedetails.tul_bit_transferin, 
	tenantunitrentraise.tur_mny_NewRent, 
	tenantunitleasedetails.tul_int_Leaseno,
	units.uni_dtm_DateCreated,
	unittyperent.futr_dtm_EffectiveDate
	FROM 
		essm.dbo.Units units,
		essm.dbo.FacilitySections sections,
		essm.dbo.UnitTypes unittypes,
		essm.dbo.FacilityUnitTypesRent unittyperent,
		essm.dbo.TenantUnitLeases tenantunitleasedetails,
		essm.dbo.StorageFacility,
		--
		essm.dbo.TenantUnitRentRaise tenantunitrentraise
		--
	WHERE  
	uni_int_SectionId = fse_int_FacilityId AND 
	uni_int_UnitTypeId=uty_int_Id and 
	uty_int_Id=futr_int_UnitTypeId and 
	--Left Outer Join
	--uni_int_UnitId *=tul_int_UnitId and
	uty_bit_DeleteFlag=0 AND 
	futr_bit_DeleteFlag = 0 AND 
	uni_bit_DeleteFlag=0 AND 
	fse_bit_DeleteFlag=0 AND 
	stf_int_FacilityId = fse_int_FacilityId and 
	tul_int_UnitId IN (
				SELECT tul_int_UnitId 
				FROM essm.dbo.TenantUnitLeases 
				WHERE tul_int_FacilityId =sections.fse_int_FacilityId) AND 
	futr_int_FacilityId = fse_int_FacilityId  AND 
	futr_dtm_EffectiveDate = (
				SELECT MAX(futr_dtm_EffectiveDate)  
				FROM essm.dbo.FacilityUnitTypesRent 
				WHERE uty_int_Id = futr_int_UnitTypeId AND 
				futr_int_FacilityId = fse_int_FacilityId AND 
				futr_bit_DeleteFlag = 0)    AND 
	uty_int_Id IN (
				SELECT futd_int_UnitTypeId  
				FROM essm.dbo.FacilityUnitTypesDeposit 
				WHERE futd_int_FacilityID =fse_int_FacilityId AND 
				futd_bit_DeleteFlag = 0)
	) AS RenAct  
ORDER BY FacilityName ASC
;