--[ Script to creaet new table ]-- CREATE TABLE LEAVE_STAFF_TRX ( DATE_TRX DATE, BADGE NUMBER(7) ) --[ Script to insert new data ]-- Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/01/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 39496); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/01/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 40386); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/01/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 201414); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/01/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 470346); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/01/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 543789); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/02/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 39496); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/02/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 40386); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/02/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 201414); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/02/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 470346); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/02/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 543789); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/03/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 39496); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/03/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 40386); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/03/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 201414); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/03/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 470346); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/03/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 543789); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/04/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 40386); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/04/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 201414); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/04/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 470346); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/04/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 543789); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/05/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 201414); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/06/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 39496); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/06/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 40386); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/06/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 201414); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/07/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 39496); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/07/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 40386); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/07/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 201414); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/07/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 470346); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/07/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 543789); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/08/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 39496); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/08/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 40386); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/08/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 201414); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/08/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 470346); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/08/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 543789); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/09/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 39496); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/09/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 470346); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/09/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 543789); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/10/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 39496); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/10/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 40386); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/10/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 201414); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/10/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 470346); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/10/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 543789); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/11/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 39496); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/11/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 40386); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/11/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 201414); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/12/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 40386); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/12/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 201414); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/13/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 39496); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/13/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 201414); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/14/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 39496); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/14/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 40386); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/14/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 201414); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/14/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 470346); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/14/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 543789); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/15/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 39496); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/15/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 201414); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/15/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 470346); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/15/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 543789); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/16/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 39496); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/16/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 40386); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/16/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 470346); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/16/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 543789); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/17/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 40386); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/17/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 470346); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/17/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 543789); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/18/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 40386); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/18/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 201414); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/18/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 470346); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/19/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 39496); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/19/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 40386); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/19/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 201414); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/20/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 39496); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/20/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 40386); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/20/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 201414); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/20/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 470346); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/20/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 543789); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/21/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 39496); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/21/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 40386); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/21/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 201414); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/21/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 470346); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/21/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 543789); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/22/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 201414); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/22/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 470346); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/22/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 543789); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/23/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 40386); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/23/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 201414); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/23/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 470346); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/23/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 543789); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/24/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 39496); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/24/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 40386); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/24/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 543789); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/25/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 39496); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/25/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 470346); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/26/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 39496); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/26/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 40386); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/26/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 201414); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/27/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 39496); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/27/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 40386); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/27/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 201414); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/27/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 543789); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/28/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 40386); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/28/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 201414); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/28/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 470346); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/28/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 543789); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/29/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 201414); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/29/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 470346); Insert into PPMSB.LEAVE_STAFF_TRAX (DATE_TRX, BADGE) Values (TO_DATE('03/29/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 543789); COMMIT; --[ Query ]-- select floor(sysdate-max(dt)) workingDays from( select distinct dt, (case when (select count(*) from leave_staff_trax where date_trx = t1.dt and badge = '201414') > 0 then 1 else 0 end) cnt from(select trunc(sysdate) as DT from dual union SELECT TRUNC (SYSDATE-rownum) dt FROM DUAL CONNECT BY ROWNUM < 30) t1 order by 1 desc) x1 where cnt =0;