PL/SQL- Query to get Start and End date Based on column Values

Question

I have a list of employees existence data by month (Exist='T', Not Exist = 'F') and data looks like following.

DepNo   Empno   Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
---------------------------------------------------------------
1234    100     T   T   T   T   T   T   T   F   F   F   F   F
1234    101     T   T   T   T   F   F   F   F   F   F   F   F
1234    102     F   F   F   F   T   T   T   T   T   T   T   T
1234    103     F   F   F   F   T   T   T   F   F   F   F   F
---------------------------------------------------------------

I want to get the Start and End dates for each employee. For e.g Employee 100 start date is 01-Jan-2015 (since in Jan he exist) and End date as 31-Jul-2015 (since employee was termintaed in August

---------------------------------------------------------------------------------
DepNo   Empno   Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec StartDt EndDt
----------------------------------------------------------------------------------
1234    100     T   T   T   T   T   T   T   F   F   F   F   F   1-Jan-15 31-Jul-15
1234    101     T   T   T   T   F   F   F   F   F   F   F   F   1-Jan-15 30-Apr-15
1234    102     F   F   F   F   T   T   T   T   T   T   T   T   1-May-15 31-Dec-15
1234    103     F   F   F   F   T   T   T   F   F   F   F   F   1-May-15 31-Jul-15
----------------------------------------------------------------------------------

I am using Oracle 11g. Can some one help me to achive this.

Answer 1

Prepare environment.

create table tab1(depno number,empno number, jan char(1),feb char(1),mar char(1),apr char(1),may char(1),jun char(1),jul char(1),aug char(1),sep char(1),oct char(1),nov char(1),dec char(1));
insert into tab1 values(1234,100,'T','T','T','T','T','T','T','F','F','F','F','F');
insert into tab1 values(1234, 101,'T','T','T','T','F','F','F','F','F','F','F','F');
insert into tab1 values(1234, 102,'F','F','F','F','T','T','T','T','T','T','T','T');
insert into tab1 values(1234,103,'F','F','F','F','T','T','T','F','F','F','F','F');

Run this query

select tab1.*,t3.startd,t3.endd from tab1, (select depno,empno,min(to_date('2015.'||monthh1||'.01','yyyy.mm.dd')) as startd, max(add_months(to_date('2015.'||monthh1||'.01','yyyy.mm.dd'),1)-1) as endd from (select t1.depno,t1.empno,decode(monthh,'JAN',1,'FEB',2,'MAR',3,'APR',4,'MAY',5,'JUN',6,'JUL',7,'AUG',8,'SEP',9,'OCT',10,'NOV',11,'DEC',12,-1) monthh1,statee from (select * from tab1 unpivot (statee for monthh in(jan,feb,mar,apr,may,jun,jul,aug,sep,oct,nov,dec))) t1) t2 where statee='T' group by depno,empno)t3 where tab1.depno=t3.depno and tab1.empno=t3.empno order by tab1.depno,tab1.empno

ENJOY.

Answer 2

Hello just tried very simplified way of resolving this issue. Hope it helps. Let me know for any issues.

    ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY';

    SELECT a.EMPNO,
      to_date('01/'
      ||COALESCE(DECODE(a.dec,'F',NULL,'12/'),DECODE(a.nov,'F',NULL,'11/'),DECODE(a.oct,'F',NULL,'10/'),DECODE(a.sep,'F',NULL,'09/'),DECODE(a.aug,'F',NULL,'08/'),DECODE(a.jul,'F',NULL,'07/'),DECODE(a.june,'F',NULL,'06/'),DECODE(a.MAY,'F',NULL,'05/'),DECODE(a.april,'F',NULL,'04/'),DECODE(a.march,'F',NULL,'03/'),DECODE(a.feb,'F',NULL,'02/'),DECODE(a.jan,'F',NULL,'01/'))
      ||extract(YEAR FROM sysdate),'DD/MM/yyyy') STR,
      TO_DATE('01/'
      ||COALESCE(DECODE(a.dec,'F',NULL,'12/'),DECODE(a.nov,'F',NULL,'11/'),DECODE(a.oct,'F',NULL,'10/'),DECODE(a.sep,'F',NULL,'09/'),DECODE(a.aug,'F',NULL,'08/'),DECODE(a.jul,'F',NULL,'07/'),DECODE(a.june,'F',NULL,'06/'),DECODE(a.MAY,'F',NULL,'05/'),DECODE(a.april,'F',NULL,'04/'),DECODE(a.march,'F',NULL,'03/'),DECODE(a.feb,'F',NULL,'02/'),DECODE(a.jan,'F',NULL,'01/'))
      ||extract(YEAR FROM sysdate),'DD/MM/yyyy') ED_DT
    FROM
      (SELECT 10 AS DPT,
        1 EMPNO,
        'T' AS jan,
        'T' AS feb,
        'T' AS march,
        'T' AS april,
        'T' AS may,
        'T' AS JUNE,
        'T' AS jul,
        'F' AS aug,
        'F' AS sep,
        'F' AS oct,
        'F' AS nov,
        'F' AS DEC
      FROM dual
      UNION ALL
      SELECT 10 AS DPT,
        2 EMPNO,
        'T' AS jan,
        'T' AS feb,
        'T' AS march,
        'T' AS april,
        'F' AS may,
        'F' AS JUNE,
        'F' AS jul,
        'F' AS aug,
        'F' AS sep,
        'F' AS oct,
        'F' AS nov,
        'F' AS DEC
      FROM dual
      UNION ALL
      SELECT 10 AS DPT,
        3 EMPNO,
        'F' AS jan,
        'F' AS feb,
        'F' AS march,
        'F' AS april,
        'T' AS may,
        'T' AS JUNE,
        'T' AS jul,
        'T' AS aug,
        'T' AS sep,
        'T' AS oct,
        'T' AS nov,
        'T' AS DEC
      FROM dual
      UNION ALL
      SELECT 10 AS DPT,
        4 EMPNO,
        'F' AS jan,
        'F' AS feb,
        'F' AS march,
        'F' AS april,
        'T' AS may,
        'T' AS JUNE,
        'T' AS jul,
        'F' AS aug,
        'F' AS sep,
        'F' AS oct,
        'F' AS nov,
        'F' AS DEC
      FROM dual
      )A;

-------------------------------OUTPUT-----------------------------------------

EMPNO     STR              ED_DT
1   01/07/2015  01/07/2015
2   01/04/2015  01/04/2015
3   01/12/2015  01/12/2015
4   01/07/2015  01/07/2015

-------------------------------OUTPUT-----------------------------------------

Answer 3

You can try something like this. Note that I strongly recommend against doing this and using PL/SQL or some other programming language for the logic. Also I have not tested this (yet). I will update based on my testing (or delete my answer)logic. Works. SQLfiddle added.

(http://sqlfiddle.com/#!4/30fc1/12):

 select e.empno, 
       1 start_day, 
       (case when EXTRACT(month FROM e.StartDt) > t.start_month 
             then EXTRACT(month FROM e.StartDt) 
             else t.start_month end) start_month, 
       EXTRACT(year FROM e.StartDt) start_year,
       to_date(1  || '-' ||
       (case when EXTRACT(month FROM e.StartDt) > t.start_month 
             then EXTRACT(month FROM e.StartDt) 
             else t.start_month end) || '-' ||
       EXTRACT(year FROM e.StartDt), 'dd-mm-yyyy') start_date
from employee e, 
     (select empno, 
             decode(Jan, 'T', 1, 
             decode(Feb, 'T', 2, 
             decode(Mar, 'T', 3, 
             decode(Apr, 'T', 4, 
             decode(May, 'T', 5, 
             decode(Jun, 'T', 6, 
             decode(Jul, 'T', 7, 
             decode(Aug, 'T', 8, 
             decode(Sep, 'T', 9, 
             decode(Oct, 'T', 10, 
             decode(Nov, 'T', 11, 
             decode(Decm, 'T', 12, 0)))))))))))) start_month
       from employee) t
where e.empno = t.empno

You can get end date based on this idea. Again, please don't use this if you can avoid.