-- Erzeuge Datenbank "datamarts"
CREATE DATABASE IF NOT EXISTS stefc
  CHARACTER SET = 'utf8' 
  COLLATE = 'utf8_german2_ci';

SET collation_connection = 'utf8_general_ci'; 
SET NAMES 'utf8' ;
SET CHARACTER SET 'utf8';

USE stefc;

CREATE TABLE IF NOT EXISTS hub_employee (
  HUB_EMPLOYEE_SK CHAR(32) NOT NULL,
  LOAD_DATE DATETIME NOT NULL,
  EMPLOYEE_ID DECIMAL(8) NOT NULL,
  
  PRIMARY KEY (HUB_EMPLOYEE_SK)
);

CREATE TABLE IF NOT EXISTS sat_employee (
  HUB_EMPLOYEE_SK CHAR(32) NOT NULL,
  LOAD_DATE DATETIME NOT NULL,
  END_DATE DATETIME,
  NAME VARCHAR(100),
  EMAIL VARCHAR(100),
  PHONE VARCHAR(100),
  
  PRIMARY KEY (HUB_EMPLOYEE_SK,LOAD_DATE),

  FOREIGN KEY (HUB_EMPLOYEE_SK)  REFERENCES hub_employee(HUB_EMPLOYEE_SK)    
);
 
DELETE FROM sat_employee;
DELETE FROM hub_employee;

INSERT INTO hub_employee VALUES 
  ( md5(4711), 20011021101200, 4711),
  ( md5(4715), 20011029102200, 4715),
  ( md5(4720), 20020102100000, 4720);

      
INSERT INTO sat_employee VALUES 
  ( md5(4711), 20011021101200, null, "Hugo", "hugo@web.de", "040-23324"),
  ( md5(4715), 20011029102200, null, "Anton", "anton@gmail.com", "089-3434"),
  ( md5(4720), 20020102100000, null, "Werner", "werner@gmx.net", "030-234555");

UPDATE sat_employee SET end_date = 20031001200000 WHERE HUB_EMPLOYEE_SK = md5(4715) AND LOAD_DATE = 20011029102200;
INSERT INTO sat_employee VALUES 
  ( md5(4715), 20031001200000, 20031001210000, "Anton", "antonia@googlemail.com", "089-3434"),
  ( md5(4715), 20031001210000, null, "Anton", "anton@googlemail.com", "089-3434");

SELECT 
  hub.HUB_EMPLOYEE_SK AS EMPLOYEE_KEY,
  hub.EMPLOYEE_ID,   
  first_value(DATE(sat.LOAD_DATE)) OVER (partition BY sat.hub_employee_sk, DATE(sat.load_date) order by sat.load_date) AS LOAD_DATE,
  last_value(sat.END_DATE) OVER (partition BY sat.hub_employee_sk, DATE(sat.load_date) order by sat.load_date) AS END_DATE,
  last_value(sat.NAME) OVER (partition BY sat.hub_employee_sk, DATE(sat.load_date) order by sat.load_date) AS NAME,
  last_value(sat.EMAIL) OVER (partition BY sat.hub_employee_sk, DATE(sat.load_date) order by sat.load_date) AS EMAIL,
  last_value(sat.PHONE) OVER (partition BY sat.hub_employee_sk, DATE(sat.load_date) order by sat.load_date) AS PHONE
FROM 
  stefc.hub_employee hub
INNER JOIN stefc.sat_employee AS sat USING(HUB_EMPLOYEE_SK)
ORDER BY hub.EMPLOYEE_ID;
     
  


