工事管理システム製品化対応

-- ?S????????????f?[?^

drop table if exists ChgChargeDep cascade;

create table ChgChargeDep (

  PersonCode  decimal(8,0) not null comment '?S????R?[?h'

  , StartDate  date not null comment '?J?n?N????'

  , CompDate  date not null comment '?I???N????'

  , DepartmentCode decimal(5,0) unsigned not null comment '?????R?[?h'

  , DepartmentName varchar(60) comment '??????'

  , EntryDate datetime not null comment '?o?^???t'

  , UpdateDate datetime not null comment '?X?V???t'

  , constraint ChgChargeDep_PKC primary key (PersonCode, StartDate)

) comment '?S????????????f?[?^' ;

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 = '?c?U??' Where PersonCode = 20150701 And DATE(StartDate) = DATE('2015/07/01');

Insert chgchargedep Value(20150701, DATE('2017/08/01'), DATE('0001/01/01'), 1, '?H????', NOW(), NOW());

update chgchargedep Set CompDate = DATE('2017/07/31'), DepartmentCode = 1, DepartmentName = '?H????' Where PersonCode = 20160602 And DATE(StartDate) = DATE('2016/06/20');

Insert chgchargedep Value(20160602, DATE('2017/08/01'), DATE('0001/01/01'), 3, '?c???', NOW(), NOW());

update chgchargedep Set CompDate = DATE('2017/07/31'), DepartmentCode = 1, DepartmentName = '?H????' Where PersonCode = 20160701 And DATE(StartDate) = DATE('2016/07/01');

Insert chgchargedep Value(20160701, DATE('2017/08/01'), DATE('0001/01/01'), 2, '?c?U??', NOW(), NOW());

commit;