Building and Infrastructures.sql

SELECT
	bi.value, -- Unique Identifier for Buildings & Infrastructures.
	bi.name, -- Name of the Building or Infrastructure.
	bi.description,
	bi.location,
	(SELECT cm.name FROM f_campus cm WHERE cm.f_campus_id = bi.f_campus_id) AS Campus,
	(SELECT c.name FROM city c WHERE c.city_id = bi.city_id) AS city,
	bi.longitude,
	bi.latitude,
	bi.dimentions,
	(SELECT u.name FROM c_uom u WHERE u.c_uom_id = bi.c_uom_id) AS uom,
	bi.purchasecost,
	bi.constructioncost,
	bi.uselifeyears,
	(SELECT s.name FROM status s WHERE s.status_id = bi.status_id) AS current_status,
	(SELECT dp.name FROM a_depreciation dp WHERE dp.a_depreciation_id = bi.a_depreciation_id) AS depreciation_method,
	bi.depreciationpercentage AS d_percentage,
	(SELECT EXTRACT(YEAR FROM bi.manufacturedyear)) AS manufactured_year,
	(SELECT EXTRACT(YEAR FROM bi.acquisitionyear)) AS aquisition_year,
	(SELECT EXTRACT(YEAR FROM CURRENT_DATE)) AS current_year,
	
	(SELECT
			mp.market_price
		FROM
			market_price mp
		WHERE
			mp.BuildingsAndInfrastructures_ID = bi.BuildingsAndInfrastructures_ID
		AND
			mp.isactive = 'Y'
		
	) AS Market_Price
	
FROM
	BuildingsAndInfrastructures bi