title: combine two record become one with unique id for large data
body: <p>I have table <code>PNS</code> for employee with <code>NIP as primary key</code>
  table <code>JADWAL</code> for schedule of employee with <code>idjadwal as primary</code>
  table <code>PRESTEMP</code> data from finger print machine that the format is 1 row for 1 time capture. </p>
  
  <p>I want to convert table <code>PRESTEMP</code> where there are stored data from finger print machine into table <code>PRESENSI</code> that the format is one row for <code>timein</code> and <code>timeout</code> indexed with column <code>NIP</code>.</p>
  
  <p>For example Table Name : PRESTEMP</p>
  
  <pre><code>NIP                  DATETIME
  196611292006041002   2019-01-01 07:01:58
  196611292006041002   2019-01-01 07:10:58
  196611292006041002   2019-01-01 14:02:58
  196611292006041002   2019-01-01 14:09:36
  </code></pre>
  
  <p>These four record I want to combine into one record depend on table JADWAL and table PNS, only the earlier time and the latest time.</p>
  
  <p>to become table name : PRESENSI, depends on table PNS and table JADWAL</p>
  
  <pre><code>NIP                   DATE          TIMEIN   TIMEOUT
  196611292006041002    2019-01-01    07:01:58  14:09:36
  </code></pre>
  
  <p>I used mysql 5.5 on UBUNTU 14.04</p>
  
  <pre><code>create temporary table impresensi (index tgl (tgl),index nip (nip), index timein (timein), index timeout (timeout), index shift (shift)) select jadwal.tgl jadwal.nip,jamkerja.timein,jamkerja.timeinpre,jamkerja.timeinpost, jamkerja.timeout, jamkerja.timeoutpre,jamkerja.timeoutpost,jamkerja.shift from (jadwal join jamkerja on((jadwal.idjamkerja = jamkerja.id))) where (tgl between dtanggal1 and dtanggal2);
  
  create temporary table impresensi1 (index nip (comnip),index timein (timein), index timeout (timeout), index tanggal (tanggal), index shift (shift)) select sql_no_cache l.id,comnip,min(comtime) timein,'00:00:00' timeout, comdate tanggal,shift,comMesinID,comdatetime diffin,null diffout from prestemp l inner join impresensi r on l.comnip = nip and comdate = r.tgl where (comtime between timeinpre and timeinpost) and (comdate between dtanggal1 and dtanggal2) group by comnip, comdate;
  
  create temporary table impresensi2 (index nip (comnip),index timein (timein), index timeout (timeout), index tanggal(tanggal), index shift (shift)) select sql_no_cache l.id,comnip,'00:00:00' timein,max(comtime) timeout, comdate tanggal,shift,comMesinID,null diffin, comdatetime diffout from prestemp l inner join impresensi r on l.comnip = r.nip and comdate = r.tgl where (comtime between timeoutpre and timeoutpost) and (comdate between dtanggal1 and dtanggal2) group by comnip,comdate;
  
  update impresensi2 set tanggal = date_add(tanggal,interval -1 day) where shift = 1;
  </code></pre>
  
  <p>YES, I solved what I want, but I got a long time to process <code>10.875 employees (on table PNS)</code> and every employee has 30 days of work schedule, it’s mean <code>326.250 records (on table JADWAL)</code>, and every employee could be have 2 or more record from finger print machine, at least <code>652.500 records</code> for a month (on table PRESTEMP) and I got <code>1.356 seconds</code> to process all of it. </p>
  
  <p>Is there is something wrong with my logical program ? please Help me to reduce time processing.</p>