hannahkates
commented
7 years ago Getting rid of this step to avoid unintended consequences Removed in b66076132856d831ecc953b25e1c17336e0114d1.
Code used to be:
SELECT cofo_job_number, (CASE WHEN u_2007_existtotal > c_u_latest THEN c_u_latest ELSE u_2007_existtotal END) as u_2007_existtotal, (CASE WHEN u_2008_existtotal > c_u_latest THEN c_u_latest ELSE u_2008_existtotal END) as u_2008_existtotal, (CASE WHEN u_2009_existtotal > c_u_latest THEN c_u_latest ELSE u_2009_existtotal END) as u_2009_existtotal, (CASE WHEN u_2010_existtotal > c_u_latest THEN c_u_latest ELSE u_2010_existtotal END) as u_2010_existtotal, (CASE WHEN u_2011_existtotal > c_u_latest THEN c_u_latest ELSE u_2011_existtotal END) as u_2011_existtotal, (CASE WHEN u_2012_existtotal > c_u_latest THEN c_u_latest ELSE u_2012_existtotal END) as u_2012_existtotal, (CASE WHEN u_2013_existtotal > c_u_latest THEN c_u_latest ELSE u_2013_existtotal END) as u_2013_existtotal, (CASE WHEN u_2014_existtotal > c_u_latest THEN c_u_latest ELSE u_2014_existtotal END) as u_2014_existtotal, (CASE WHEN u_2015_existtotal > c_u_latest THEN c_u_latest ELSE u_2015_existtotal END) as u_2015_existtotal, (CASE WHEN u_2016_existtotal > c_u_latest THEN c_u_latest ELSE u_2016_existtotal END) as u_2016_existtotal, (CASE WHEN u_2017_existtotal > c_u_latest THEN c_u_latest ELSE u_2017_existtotal END) as u_2017_existtotal, c_u_latest, c_date_latest, c_date_earliest, c_type_latest FROM dob_cofos_byjob_unitslatest
Not sure we should trust Step 3 as is.
Step 3 was included because there were cases of projects where the number of units erroneously increased and then decreased over a project's lifespan due to data entry errors. However, there are many cases of building alterations where units are taken out of commission while renovations are being done, so an earlier CofO would have a higher unit count than the latest CofO's if the project is still being developed. The current approach also assumes that the number of units should always be increasing which isn't always the case. Some alterations will result in a reduction of units.