Lease dtatbase

Instructions.

Tasks Summary:


1. Migrate the database to a new google account.
2. Add PTAL Data to the Google Cloud database for all postcodes in London and add codes to the current SQL codes to pull the data into the template.
3. Add Flood Data for all postcodes throughout the UK to the Google Cloud database. [skip for now, if too complicated]
4. Add Retention Scheme Data to the Google Cloud database and add codes to the current SQL codes to pull the data into the template.
5. Add codes to the current SQL codes to pull out the “insolvency” and “charges” data into a different tab of the same template sheet. [data sample, and updated templated is provided]
6. Upload “Site and Lease Info” Information to the Google Cloud Database and add codes to the current SQL codes to pull the data into a different tab of the same template sheet.


• Changes in Template:
o Columns CO-CS have been added for “Retention Scheme Data”.
o Column DR-DS has been added for Flood Zone and PTAL data.
o New tabs; “insolvency” and “charges”, “Site and Lease Info” have been added.

----------------------------------


• Rename the table before migration:
o CCOD - “Base Data”
o Insolvency - “Insolvency”
o London Postcode - “Postcodes - London Only”
o Namesor - “Officer Ethnicity”
o Property Price - “Area Prices”
o Property Yield - “Area Yield”
o Zoopla - “Zoopla”
o Charges - “Company Charges”
o Company Information - “Company Details”
o Company Officers - “Officers Data”


 you can leave the remaining table as it is.
• Add PTAL Data to the Google Cloud database for all postcodes in London and add codes to the current SQL codes to pull the data into the template.


• PTAL
o Website:
 https://tfl.gov.uk/info-for/urban-planning-and-construction/planning-with-webcat/webcat





• Use screengrab or API (if available to get data out for unique postcodes from CCOD).
• Add PTAL Data to Column CT in the output file.








• Add Flood Data for all postcodes throughout the UK to the Google Cloud database. [skip for now, if too complicated]


• Website:
o https://flood-map-for-planning.service.gov.uk/ (For Screengrab)
o https://environment.data.gov.uk/flood-monitoring/doc/reference (API)
• Use screengrab or API (if available to get data out for unique postcodes from CCOD).
• Add Flood Data to column CU in the Output file.

----------------------------------



• Add Retention Scheme Data to the Google Cloud database and add codes to the current SQL codes to pull the data into the template.


• Upload the “Retention Scheme Data” file to the cloud.
• Add new codes to pull out data for relevant companies into the output file from the Retention Scheme Data.
• Data to be filled in Column CO-CS in the output file.

----------------------------------


• Add codes to the current SQL codes to pull out the “insolvency” and “charges” data into a different tab of the same sheet. [data sample, and updated templated is provided]


• I have provided an updated template.
• Sample data for charges and Insolvency also will be provided in the new template.
• Add codes to the current SQL codes to pull out the “insolvency” and “charges” data to be added to a separate tab of the same sheet on the new template like shown in the new sample data provided. [sample data in the insolvency and charges tab in the new template]

----------------------------------





• Upload “Site and Lease Info” Information to the Google Cloud Database and add codes to the current SQL codes to pull the data into a different tab of the same template sheet.

• Upload “Site and Lease Info” to the Google Cloud Database.
• Use “Title Number” to connect between Data from CCOD to “Site and Lease Info”
• A single title number may have multiple results, so we need to pull out data into a different tab of the same template sheet, similar to charges and insolvency.
• Add codes to the current SQL codes to pull out the “Site and Lease Info” into a different tab of the same template sheet.
• “Site and Lease Info” to be added in Column CO-DL on the template.
• Link to “Site and Lease Info” below, you can combine sheets for all regions into one single sheet after the upload is complet.:
• https://drive.google.com/drive/folders/1Bw_Ms2SD_79wxk1axd9Ll78bYk5ovuZy?usp=sharing


Bugs to Fix:




1. Gross Yield needs to be changed to Percentage (%).




2. Remove the “” from the prices from Zoopla.



3. The Average price for Semi-Detached is not the average price per sqf. Please fix . Below is a screenshot of Semi-Detached Average price per SQF.