Purpose: Preparing the table for processing in Power BI involves data cleaning, aligning data types, calculating the age and length of service in the company for each employee.

Data Structure:The Human table in SSMS contains data about employees, including their date of birth, first name, last name, date of hire and date of termination (if the employee has left), and personal information about the employee.

Operations with data in SQL:
• 1. First, we will check the data types in the table to ensure they are displayed correctly. To do this, we will write a query:

  
      --checking data type
  SELECT COLUMN_NAME, DATA_TYPE
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = 'Human';
      }
  

• 2. The client also wants to replace "Last_name" with "Surname" and "Team" with "Squad". We will do this for them by renaming the columns.

  
      exec sp_rename '[projects].[dbo].[Human].last_name','Surname','Column';
  exec sp_rename '[projects].[dbo].[Human].location','Squard','Column';
  UPDATE Human
      

• 3. Unfortunately, the data types for birthdate, termdate, and Age are set incorrectly. Additionally, termdate is set with errors and uses different date separators. Let's start by correcting the type of birthdate from varchar to date:

  
      -- change data type for Birthdate
  
  UPDATE Human
  SET birthdate = 
      CASE
          
          WHEN birthdate LIKE '%-%' THEN CONVERT(DATE, birthdate, 102)
          ELSE NULL
      END;
  
  
  ALTER TABLE Human
  ALTER COLUMN birthdate DATE;
  
  --Let's do the same for term_date as well:
  --change type of termDate
  UPDATE Human
  SET termdate = CONVERT(date, LEFT(termdate, 10), 126)
  WHERE termdate IS NOT NULL AND termdate != '';
  
  
  ALTER TABLE Human
  ALTER COLUMN hire_date DATE;
  
  --And change data type for Age column:
  --age selection for PBI
  alter table Human
  add AgeRange INT;
  
      

• 4.Let’s jump to the calculation of employee’s age. For this task, create a query:

  
      --calculate age of employee
  update Human
  set Age = DATEDIFF(YEAR,birthdate,GETDATE());
      

  and calculate the age type of employee as our client wants. For this reason, we can use case to divide all employees of to three age groups

  
        - young, middle and senior:
  --add Age type
  alter table Human
  add AgeType Varchar(20);
  
  --add age type from table
  
  UPDATE Human
  SET AgeType =
      CASE 
          WHEN Age BETWEEN 18 AND 30 THEN 'young'
          WHEN Age BETWEEN 31 AND 45 THEN 'middle'
          WHEN Age > 45 THEN 'senior'
          ELSE NULL
      END;
  
      

• 5.What is the duration for each employee to work in the company? For resolve this task, we need to use our old good chap - case as in our previous step when we calculate AgeType:

  
      -year in the company
  --add time column
  alter table Human
  add Duration VARCHAR(50);
  
  ALTER TABLE Human
  ALTER COLUMN Duration VARCHAR(50);
  
  --calculate duration emplyeing in the company
  UPDATE Human
  SET Duration = 
      CASE 
          WHEN termdate IS NULL THEN 'Working'
          WHEN DATEDIFF(year, hire_date, termdate) = 0 THEN 'Working'
          ELSE CAST(DATEDIFF(year, hire_date, termdate) AS VARCHAR(50))
      END;
  
       

  Our data is ready and waiting to be imported into Power BI. Therefore, we can easily proceed with it.