Ilmu Sql

select
level_1, level_2, level_3, level_4, level_5,
idemp_1, idemp_2, idemp_3, idemp_4, idemp_5,
b.officialname emp_1, c.officialname emp_2, d.officialname emp_3, e.officialname emp_4, f.officialname emp_5,
case
    when start_1>=start_2 and start_1>=start_3 and start_1>=start_4 and start_1>=start_5 then start_1
    when start_2>=start_1 and start_2>=start_3 and start_2>=start_4 and start_2>=start_5 then start_2
    when start_3>=start_1 and start_3>=start_2 and start_3>=start_4 and start_3>=start_5 then start_3
    when start_4>=start_1 and start_4>=start_2 and start_4>=start_3 and start_4>=start_5 then start_4
    when start_5>=start_1 and start_5>=start_2 and start_5>=start_3 and start_5>=start_4 then start_5
end startdate, enddate
from (
    select
    level_1, level_2, level_3, level_4, level_5,
    idemp_1, idemp_2, idemp_3, idemp_4, idemp_5, start_1, start_2, start_3, start_4, start_5,
    case
        when end_1<=end_2 and end_1<=end_3 and end_1<=end_4 and end_1<=end_5 then end_1
        when end_2<=end_1 and end_2<=end_3 and end_2<=end_4 and end_2<=end_5 then end_2
        when end_3<=end_1 and end_3<=end_2 and end_3<=end_4 and end_3<=end_5 then end_3
        when end_4<=end_1 and end_4<=end_2 and end_4<=end_3 and end_4<=end_5 then end_4
        when end_5<=end_1 and end_5<=end_2 and end_5<=end_3 and end_5<=end_4 then end_5
    end enddate
    from (
        select
        level_1, level_2, level_3, level_4, level_5,
        b.idemp idemp_1, c.idemp idemp_2, d.idemp idemp_3, e.idemp idemp_4, f.idemp idemp_5,
        b.startdate start_1, b.enddate end_1,
        c.startdate start_2, c.enddate end_2,
        d.startdate start_3, d.enddate end_3,
        e.startdate start_4, e.enddate end_4,
        f.startdate start_5, f.enddate end_5
        from (
            select distinct a.idsubcat, a.idcovjob level_1, a.idcovjobpar level_2, b.idcovjobpar level_3, c.idcovjobpar level_4, d.idcovjobpar level_5
            from mcoverjobhistory a
            left join MCoverJobHistory b on (a.IdCovJobPar=b.IdCovJob)
            left join MCoverJobHistory c on (b.IdCovJobPar=c.IdCovJob)
            left join MCoverJobHistory d on (c.IdCovJobPar=d.IdCovJob)
            where a.isactive='T' and b.isactive='T' and c.isactive='T' and d.isactive='T'
            and a.idsubcat=31 and b.idsubcat=31 and c.idsubcat=31 and c.idsubcat=31
            and a.idjob=100
        ) a
        inner join mcovjobemployee b on (a.level_1=b.idcovjob and b.isactive='T')
        inner join mcovjobemployee c on (a.level_2=c.idcovjob and c.isactive='T')
        inner join mcovjobemployee d on (a.level_3=d.idcovjob and d.isactive='T')
        inner join mcovjobemployee e on (a.level_4=e.idcovjob and e.isactive='T')
        inner join mcovjobemployee f on (a.level_5=f.idcovjob and f.isactive='T')
    ) a
) a
inner join memployee b on (a.idemp_1=b.idemp)
inner join memployee c on (a.idemp_2=c.idemp)
inner join memployee d on (a.idemp_3=d.idemp)
inner join memployee e on (a.idemp_4=e.idemp)
inner join memployee f on (a.idemp_5=f.idemp)
order by 1, startdate