Skip to Main Content
  • Questions

  • Data to be displayed grouped by start and end date

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: February 17, 2017 - 9:32 pm UTC

Last updated: February 20, 2017 - 1:22 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

I have a table t




I have a table t

Create table t (employee varchar2(10), (job varchar2(5), start_date date, end_date date, FTE int );

with this data in it:

insert into t values (1111,1,19-May-2008,18-May-2010,100);
insert into t values (1111,1,19-May-2010,30-Jun-2010,100);
insert into t values (1111,1,01-Jul-2010,13-Feb-2011,100);
insert into t values (1111,1,14-Feb-2011,13-Feb-2012,100);
insert into t values (1111,1,14-Feb-2012,13-Feb-2013,100);
insert into t values (1111,1,14-Feb-2013,27-Nov-2013,100);
insert into t values (1111,1,15-Nov-2013,27-Nov-2013,100);
insert into t values (1111,1,28-Nov-2013,24-Aug-2014,60);
insert into t values (1111,1,25-Aug-2014,24-Aug-2015,60);
insert into t values (1111,1,15-Nov-2014,24-Aug-2015,60);
insert into t values (1111,1,25-Aug-2015,14-Nov-2015,68.571);
insert into t values (1111,1,15-Nov-2015,31-Mar-2016,68.571);
insert into t values (1111,1,01-Apr-2016,11-Sep-2016,68.571);
insert into t values (1111,1,12-Sep-2016,29-Sep-2016,72.857);
insert into t values (1111,1,30-Sep-2016,10-Nov-2016,91.429);
insert into t values (1111,1,11-Nov-2016,14-Nov-2016,100);
insert into t values (1111,1,15-Nov-2016,23-Apr-2017,100);
insert into t values (1111,1,24-Apr-2017,14-Jan-2018,60);
insert into t values (1111,1,15-Jan-2018,31-May-2019,71.429);
insert into t values (1111,1,01-Jun-2019,31-Dec-2049,100);

I want the FTE data to be displayed grouped by correct startdate and end date , so that it appears like this

1111 1 19/05/2008 27/11/2013 100
1111 1 28/11/2013 24/08/2015 60
1111 1 25/08/2015 11/09/2016 68.571
1111 1 12/09/2016 29/09/2016 72.857
1111 1 30/09/2016 10/11/2016 91.429
1111 1 11/11/2016 23/04/2017 100
1111 1 24/04/2017 14/01/2018 60
1111 1 15/01/2018 31/05/2019 71.429
1111 1 1/06/2019 31/12/2049 100

How can I achieve this?

and Connor said...

You can't achieve it ... because your test case isn't close to working :-)

SQL> create table t (employee varchar2(10), (job varchar2(5), start_date date, end_date date, FTE int );
create table t (employee varchar2(10), (job varchar2(5), start_date date, end_date date, FTE int )
                                       *
ERROR at line 1:
ORA-00904: : invalid identifier


SQL>
SQL> insert into t values (1111,1,19-May-2008,18-May-2010,100);
insert into t values (1111,1,19-May-2008,18-May-2010,100)
            *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
SQL> insert into t values (1111,1,19-May-2010,30-Jun-2010,100);
insert into t values (1111,1,19-May-2010,30-Jun-2010,100)
            *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
SQL> insert into t values (1111,1,01-Jul-2010,13-Feb-2011,100);
insert into t values (1111,1,01-Jul-2010,13-Feb-2011,100)
            *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
SQL> insert into t values (1111,1,14-Feb-2011,13-Feb-2012,100);
insert into t values (1111,1,14-Feb-2011,13-Feb-2012,100)
            *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
SQL> insert into t values (1111,1,14-Feb-2012,13-Feb-2013,100);
insert into t values (1111,1,14-Feb-2012,13-Feb-2013,100)
            *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
SQL> insert into t values (1111,1,14-Feb-2013,27-Nov-2013,100);
insert into t values (1111,1,14-Feb-2013,27-Nov-2013,100)
            *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
SQL> insert into t values (1111,1,15-Nov-2013,27-Nov-2013,100);
insert into t values (1111,1,15-Nov-2013,27-Nov-2013,100)
            *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
SQL> insert into t values (1111,1,28-Nov-2013,24-Aug-2014,60);
insert into t values (1111,1,28-Nov-2013,24-Aug-2014,60)
            *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
SQL> insert into t values (1111,1,25-Aug-2014,24-Aug-2015,60);
insert into t values (1111,1,25-Aug-2014,24-Aug-2015,60)
            *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
SQL> insert into t values (1111,1,15-Nov-2014,24-Aug-2015,60);
insert into t values (1111,1,15-Nov-2014,24-Aug-2015,60)
            *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
SQL> insert into t values (1111,1,25-Aug-2015,14-Nov-2015,68.571);
insert into t values (1111,1,25-Aug-2015,14-Nov-2015,68.571)
            *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
SQL> insert into t values (1111,1,15-Nov-2015,31-Mar-2016,68.571);
insert into t values (1111,1,15-Nov-2015,31-Mar-2016,68.571)
            *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
SQL> insert into t values (1111,1,01-Apr-2016,11-Sep-2016,68.571);
insert into t values (1111,1,01-Apr-2016,11-Sep-2016,68.571)
            *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
SQL> insert into t values (1111,1,12-Sep-2016,29-Sep-2016,72.857);
insert into t values (1111,1,12-Sep-2016,29-Sep-2016,72.857)
            *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
SQL> insert into t values (1111,1,30-Sep-2016,10-Nov-2016,91.429);
insert into t values (1111,1,30-Sep-2016,10-Nov-2016,91.429)
            *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
SQL> insert into t values (1111,1,11-Nov-2016,14-Nov-2016,100);
insert into t values (1111,1,11-Nov-2016,14-Nov-2016,100)
            *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
SQL> insert into t values (1111,1,15-Nov-2016,23-Apr-2017,100);
insert into t values (1111,1,15-Nov-2016,23-Apr-2017,100)
            *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
SQL> insert into t values (1111,1,24-Apr-2017,14-Jan-2018,60);
insert into t values (1111,1,24-Apr-2017,14-Jan-2018,60)
            *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
SQL> insert into t values (1111,1,15-Jan-2018,31-May-2019,71.429);
insert into t values (1111,1,15-Jan-2018,31-May-2019,71.429)
            *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
SQL> insert into t values (1111,1,01-Jun-2019,31-Dec-2049,100);
insert into t values (1111,1,01-Jun-2019,31-Dec-2049,100)
            *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>



Even when I corrected your 'create table'...


SQL> drop table t purge;
drop table t purge
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
SQL> create table t (employee varchar2(10), job varchar2(5), start_date date, end_date date, FTE int );

Table created.

SQL>
SQL> insert into t values (1111,1,19-May-2008,18-May-2010,100);
insert into t values (1111,1,19-May-2008,18-May-2010,100)
                                            *
ERROR at line 1:
ORA-00984: column not allowed here


SQL>
SQL> insert into t values (1111,1,19-May-2010,30-Jun-2010,100);
insert into t values (1111,1,19-May-2010,30-Jun-2010,100)
                                            *
ERROR at line 1:
ORA-00984: column not allowed here


SQL>
SQL> insert into t values (1111,1,01-Jul-2010,13-Feb-2011,100);
insert into t values (1111,1,01-Jul-2010,13-Feb-2011,100)
                                            *
ERROR at line 1:
ORA-00984: column not allowed here


SQL>
SQL> insert into t values (1111,1,14-Feb-2011,13-Feb-2012,100);
insert into t values (1111,1,14-Feb-2011,13-Feb-2012,100)
                                            *
ERROR at line 1:
ORA-00984: column not allowed here


SQL>
SQL> insert into t values (1111,1,14-Feb-2012,13-Feb-2013,100);
insert into t values (1111,1,14-Feb-2012,13-Feb-2013,100)
                                            *
ERROR at line 1:
ORA-00984: column not allowed here


SQL>
SQL> insert into t values (1111,1,14-Feb-2013,27-Nov-2013,100);
insert into t values (1111,1,14-Feb-2013,27-Nov-2013,100)
                                            *
ERROR at line 1:
ORA-00984: column not allowed here


SQL>
SQL> insert into t values (1111,1,15-Nov-2013,27-Nov-2013,100);
insert into t values (1111,1,15-Nov-2013,27-Nov-2013,100)
                                            *
ERROR at line 1:
ORA-00984: column not allowed here


SQL>
SQL> insert into t values (1111,1,28-Nov-2013,24-Aug-2014,60);
insert into t values (1111,1,28-Nov-2013,24-Aug-2014,60)
                                            *
ERROR at line 1:
ORA-00984: column not allowed here


SQL>
SQL> insert into t values (1111,1,25-Aug-2014,24-Aug-2015,60);
insert into t values (1111,1,25-Aug-2014,24-Aug-2015,60)
                                            *
ERROR at line 1:
ORA-00984: column not allowed here


SQL>
SQL> insert into t values (1111,1,15-Nov-2014,24-Aug-2015,60);
insert into t values (1111,1,15-Nov-2014,24-Aug-2015,60)
                                            *
ERROR at line 1:
ORA-00984: column not allowed here


SQL>
SQL> insert into t values (1111,1,25-Aug-2015,14-Nov-2015,68.571);
insert into t values (1111,1,25-Aug-2015,14-Nov-2015,68.571)
                                            *
ERROR at line 1:
ORA-00984: column not allowed here


SQL>
SQL> insert into t values (1111,1,15-Nov-2015,31-Mar-2016,68.571);
insert into t values (1111,1,15-Nov-2015,31-Mar-2016,68.571)
                                            *
ERROR at line 1:
ORA-00984: column not allowed here


SQL>
SQL> insert into t values (1111,1,01-Apr-2016,11-Sep-2016,68.571);
insert into t values (1111,1,01-Apr-2016,11-Sep-2016,68.571)
                                            *
ERROR at line 1:
ORA-00984: column not allowed here


SQL>
SQL> insert into t values (1111,1,12-Sep-2016,29-Sep-2016,72.857);
insert into t values (1111,1,12-Sep-2016,29-Sep-2016,72.857)
                                            *
ERROR at line 1:
ORA-00984: column not allowed here


SQL>
SQL> insert into t values (1111,1,30-Sep-2016,10-Nov-2016,91.429);
insert into t values (1111,1,30-Sep-2016,10-Nov-2016,91.429)
                                            *
ERROR at line 1:
ORA-00984: column not allowed here


SQL>
SQL> insert into t values (1111,1,11-Nov-2016,14-Nov-2016,100);
insert into t values (1111,1,11-Nov-2016,14-Nov-2016,100)
                                            *
ERROR at line 1:
ORA-00984: column not allowed here


SQL>
SQL> insert into t values (1111,1,15-Nov-2016,23-Apr-2017,100);
insert into t values (1111,1,15-Nov-2016,23-Apr-2017,100)
                                            *
ERROR at line 1:
ORA-00984: column not allowed here


SQL>
SQL> insert into t values (1111,1,24-Apr-2017,14-Jan-2018,60);
insert into t values (1111,1,24-Apr-2017,14-Jan-2018,60)
                                            *
ERROR at line 1:
ORA-00984: column not allowed here


SQL>
SQL> insert into t values (1111,1,15-Jan-2018,31-May-2019,71.429);
insert into t values (1111,1,15-Jan-2018,31-May-2019,71.429)
                                            *
ERROR at line 1:
ORA-00984: column not allowed here


SQL>
SQL> insert into t values (1111,1,01-Jun-2019,31-Dec-2049,100);
insert into t values (1111,1,01-Jun-2019,31-Dec-2049,100)
                                            *
ERROR at line 1:
ORA-00984: column not allowed here


SQL>



Part of a good test case .... is that *you* test it :-)

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

after fixing those errors

Rajeshwaran, Jeyabal, February 20, 2017 - 8:01 am UTC

Not sure why do you have "employee" and "job" field defined in Varchar2 and have numeric values in them?

Have right datatypes applied to your data.

demo@ORA12C> set feedback off
demo@ORA12C> drop table t purge;
demo@ORA12C> Create table t (employee int, job int, start_date date, end_date date, FTE number(10,3) );
demo@ORA12C> insert into t values (1111,1,to_date('19-May-2008','dd-mon-yyyy'),to_date('18-May-2010','dd-mon-yyyy'),100);
demo@ORA12C> insert into t values (1111,1,to_date('19-May-2010','dd-mon-yyyy'),to_date('30-Jun-2010','dd-mon-yyyy'),100);
demo@ORA12C> insert into t values (1111,1,to_date('01-Jul-2010','dd-mon-yyyy'),to_date('13-Feb-2011','dd-mon-yyyy'),100);
demo@ORA12C> insert into t values (1111,1,to_date('14-Feb-2011','dd-mon-yyyy'),to_date('13-Feb-2012','dd-mon-yyyy'),100);
demo@ORA12C> insert into t values (1111,1,to_date('14-Feb-2012','dd-mon-yyyy'),to_date('13-Feb-2013','dd-mon-yyyy'),100);
demo@ORA12C> insert into t values (1111,1,to_date('14-Feb-2013','dd-mon-yyyy'),to_date('27-Nov-2013','dd-mon-yyyy'),100);
demo@ORA12C> insert into t values (1111,1,to_date('15-Nov-2013','dd-mon-yyyy'),to_date('27-Nov-2013','dd-mon-yyyy'),100);
demo@ORA12C> insert into t values (1111,1,to_date('28-Nov-2013','dd-mon-yyyy'),to_date('24-Aug-2014','dd-mon-yyyy'),60);
demo@ORA12C> insert into t values (1111,1,to_date('25-Aug-2014','dd-mon-yyyy'),to_date('24-Aug-2015','dd-mon-yyyy'),60);
demo@ORA12C> insert into t values (1111,1,to_date('15-Nov-2014','dd-mon-yyyy'),to_date('24-Aug-2015','dd-mon-yyyy'),60);
demo@ORA12C> insert into t values (1111,1,to_date('25-Aug-2015','dd-mon-yyyy'),to_date('14-Nov-2015','dd-mon-yyyy'),68.571);
demo@ORA12C> insert into t values (1111,1,to_date('15-Nov-2015','dd-mon-yyyy'),to_date('31-Mar-2016','dd-mon-yyyy'),68.571);
demo@ORA12C> insert into t values (1111,1,to_date('01-Apr-2016','dd-mon-yyyy'),to_date('11-Sep-2016','dd-mon-yyyy'),68.571);
demo@ORA12C> insert into t values (1111,1,to_date('12-Sep-2016','dd-mon-yyyy'),to_date('29-Sep-2016','dd-mon-yyyy'),72.857);
demo@ORA12C> insert into t values (1111,1,to_date('30-Sep-2016','dd-mon-yyyy'),to_date('10-Nov-2016','dd-mon-yyyy'),91.429);
demo@ORA12C> insert into t values (1111,1,to_date('11-Nov-2016','dd-mon-yyyy'),to_date('14-Nov-2016','dd-mon-yyyy'),100);
demo@ORA12C> insert into t values (1111,1,to_date('15-Nov-2016','dd-mon-yyyy'),to_date('23-Apr-2017','dd-mon-yyyy'),100);
demo@ORA12C> insert into t values (1111,1,to_date('24-Apr-2017','dd-mon-yyyy'),to_date('14-Jan-2018','dd-mon-yyyy'),60);
demo@ORA12C> insert into t values (1111,1,to_date('15-Jan-2018','dd-mon-yyyy'),to_date('31-May-2019','dd-mon-yyyy'),71.429);
demo@ORA12C> insert into t values (1111,1,to_date('01-Jun-2019','dd-mon-yyyy'),to_date('31-Dec-2049','dd-mon-yyyy'),100);
demo@ORA12C> commit;
demo@ORA12C> set feedback 6
demo@ORA12C>


Incase of Oracle 11g database, we can make use of Analytics.

demo@ORA12C> select employee,job,min(start_date),max(end_date),fte,count(*)
  2  from (
  3  select employee,job,start_date,end_date,fte,
  4            last_value( rn ignore nulls ) over(order by employee,job,start_date) grp
  5  from (
  6  select t.*,
  7        case when lag(fte) over(order by employee,job,start_date) is null or
  8          fte <> lag(fte) over(order by employee,job,start_date) then
  9          row_number() over(order by employee,job,start_date) end rn
 10  from t
 11       )
 12       )
 13  group by employee,job,fte,grp
 14  order by 3 ;

  EMPLOYEE        JOB MIN(START_D MAX(END_DAT        FTE   COUNT(*)
---------- ---------- ----------- ----------- ---------- ----------
      1111          1 19-MAY-2008 27-NOV-2013        100          7
      1111          1 28-NOV-2013 24-AUG-2015         60          3
      1111          1 25-AUG-2015 11-SEP-2016     68.571          3
      1111          1 12-SEP-2016 29-SEP-2016     72.857          1
      1111          1 30-SEP-2016 10-NOV-2016     91.429          1
      1111          1 11-NOV-2016 23-APR-2017        100          2
      1111          1 24-APR-2017 14-JAN-2018         60          1
      1111          1 15-JAN-2018 31-MAY-2019     71.429          1
      1111          1 01-JUN-2019 31-DEC-2049        100          1

9 rows selected.


Where as in 12c - we could use MATCH_RECOGNIZE feature to solve this.

demo@ORA12C> select *
  2  from t
  3  match_recognize(
  4    order by start_date
  5    measures
  6      min(start_date) as start_date,
  7      max(end_date) as end_date,
  8      employee as employee,
  9      job as jobs,
 10      fte as fte,
 11      count(*) as cnt
 12    one row per match
 13    pattern( strt down* )
 14    define
 15      down as fte = strt.fte )
 16  order by 1   ;

START_DATE  END_DATE      EMPLOYEE       JOBS        FTE        CNT
----------- ----------- ---------- ---------- ---------- ----------
19-MAY-2008 27-NOV-2013       1111          1        100          7
28-NOV-2013 24-AUG-2015       1111          1         60          3
25-AUG-2015 11-SEP-2016       1111          1     68.571          3
12-SEP-2016 29-SEP-2016       1111          1     72.857          1
30-SEP-2016 10-NOV-2016       1111          1     91.429          1
11-NOV-2016 23-APR-2017       1111          1        100          2
24-APR-2017 14-JAN-2018       1111          1         60          1
15-JAN-2018 31-MAY-2019       1111          1     71.429          1
01-JUN-2019 31-DEC-2049       1111          1        100          1

9 rows selected.

demo@ORA12C>

Connor McDonald
February 20, 2017 - 1:22 pm UTC

https://goo.gl/yP37pQ
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.