-- 担当者部署履歴データ
drop table if exists ChgChargeDep cascade;

create table ChgChargeDep (
  PersonCode  decimal(8,0) not null comment '担当者コード'
  , StartDate  date not null comment '開始年月日'
  , CompDate  date not null comment '終了年月日'
  , DepartmentCode decimal(5,0) unsigned not null comment '部署コード'
  , DepartmentName varchar(60) comment '部署名'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint ChgChargeDep_PKC primary key (PersonCode, StartDate)
) comment '担当者部署履歴データ' ;

Insert INTO ChgChargeDep
select
  A.PersonCode
  , A.StartDate
  , A.EndDate
  , A.DepartmentCode
  , B.DepartmentString
  , Now()
  , Now() 
From
  personinchargemaster As A
  Left Join departmentmaster As B
  On B.DepartmentCode = A.DepartmentCode
;

update chgchargedep Set CompDate = DATE('2017/07/31'), DepartmentCode = 2, DepartmentName = '営繕部' Where PersonCode = 20150701 And DATE(StartDate) = DATE('2015/07/01');
Insert chgchargedep Value(20150701, DATE('2017/08/01'), DATE('0001/01/01'), 1, '工事部', NOW(), NOW());

update chgchargedep Set CompDate = DATE('2017/07/31'), DepartmentCode = 1, DepartmentName = '工事部' Where PersonCode = 20160602 And DATE(StartDate) = DATE('2016/06/20');
Insert chgchargedep Value(20160602, DATE('2017/08/01'), DATE('0001/01/01'), 3, '営業部', NOW(), NOW());

update chgchargedep Set CompDate = DATE('2017/07/31'), DepartmentCode = 1, DepartmentName = '工事部' Where PersonCode = 20160701 And DATE(StartDate) = DATE('2016/07/01');
Insert chgchargedep Value(20160701, DATE('2017/08/01'), DATE('0001/01/01'), 2, '営繕部', NOW(), NOW());
commit;