Preppin' Data

Created by: Tom Smith (Twitter: @tSmithyyy ) You work for a Property Management company and your boss has asked you to compile a report detailing how much revenue the business can expect to make from its existing office space rental contracts. The time frame in focus is from 2021 until 2026 and the board would like to see a cumulative total for each month of a contract. An additional requirement is to create a dynamic table that totals rent paid for completed years across all contracts, as well as year to date figures for the current year, which would update as time goes on. Input files: Rental Contracts Office Space Prices Requirements: Input the Rental Contracts data Work out the length of each contract in months Work out the number of months until each contract expires (imagine today is 13th April 2022) Input the Office Space Prices data and join it to the contracts table Remove duplicated fields Create a row for each month that a rental contract will be live - Retain the details f...

Challenge by: Jenny Martin I was talking to a friend the other day who is a big football fan, but unfortunately their team hasn't been doing too well in recent years. They support a local team called Southend United and my ears pricked up when he claimed they've only won 18 games in the last 108 that they've played. My initial intention with this challenge was to investigate this claim, but instead I stumbled upon historical data which I though might help him to feel a little better and remember the good times. Input We're using the Football Club History Database  for our data this week: Requirements Input the data Rename the penultimate column from P 1 (as it appears in Prep) to Pts Exclude null rows Create a Special Circumstances field with the following categories Incomplete (for the most recent season) Abandoned due to WW2 (for the 1939 season) N/A for full seasons Ensure the POS field only has values for full seasons Extract the numeric values from the leagues F...