5 Replies Latest reply on Mar 31, 2017 6:16 AM by Etbin

    Calculate a Pivot with Start and Duration

    martin_wolz

      Hey!

       

      I hope you can help me!

      I want to draw a chart with the workload. My Input Table has the Task Name, a Group Name the Start Date and the Duration (In the picture the first one).

      Now I would like to have a kind of Pivot Table where I can see the sum of all days who are involved (Secound Table in the Picture).

       

      At the moment my I only calculate with calenderweeks and have a Query for each week (If Duration/7>1 then CW+1)...

      But now I woul like to calculate everything with a day. That would mean that I have up to 30 Querys (my maximum of Duration is 30).

       

       

      It would be great If someone could help me. After searching a long time I decide to ask you guys!

       

      Best Regards

       

      Martin

       

      Edit:

      I use Oracle Database 11g Express Edition Release 11.2.0.2.0

       

      CREATE TABLE TABLEINPUT
      (
        ID NUMBER NOT NULL
      , TASKNAME VARCHAR2(20)
      , GROUPID VARCHAR2(20)
      , STARTDATE DATE
      , DURATION NUMBER
      , CONSTRAINT TABLEINPUT_PK PRIMARY KEY
        (
          ID
        )
        ENABLE
      );
      

       

      INSERT INTO "BMW"."TABLEINPUT" (ID, TASKNAME, GROUPID, STARTDATE, DURATION) VALUES ('1', 'Task1', '1', TO_DATE('2017-04-03 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '10')
      INSERT INTO "BMW"."TABLEINPUT" (ID, TASKNAME, GROUPID, STARTDATE, DURATION) VALUES ('2', 'Task2', '1', TO_DATE('2017-04-03 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '15')
      INSERT INTO "BMW"."TABLEINPUT" (ID, TASKNAME, GROUPID, STARTDATE, DURATION) VALUES ('3', 'Task3', '1', TO_DATE('2017-04-10 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '5')
      INSERT INTO "BMW"."TABLEINPUT" (ID, TASKNAME, GROUPID, STARTDATE, DURATION) VALUES ('4', 'Task4', '2', TO_DATE('2017-04-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '5')
      INSERT INTO "BMW"."TABLEINPUT" (ID, TASKNAME, GROUPID, STARTDATE, DURATION) VALUES ('5', 'Task5', '2', TO_DATE('2017-04-03 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '13')
      

       

      Nachricht geändert durch martin_wolz

        • 1. Re: Calculate a Pivot with Start and Duration
          Frank Kulash

          Hi,

           

          Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved, so that the people who want to help you can re-create the problem and test their ideas.

          Also post the exact results you want from that data, and an explanation of how you get those results from that data, with specific examples.

          Simplify the problem as much as possible.  For example, you may need 30 columns of output, but would you have the same problem if you only wanted 3 column?  If so, post sample data and desired output just for those 3 columns. 

          Always say which version of Oracle you're using (for example, 11.2.0.2.0).

          See the forum FAQ: Re: 2. How do I ask a question on the forums?

          • 2. Re: Calculate a Pivot with Start and Duration
            Tércio Costa

            Please, put here some create table statements and the desired output. Some sample data.

            • 3. Re: Calculate a Pivot with Start and Duration
              Frank Kulash

              Hi,

               

              It looks like you changed your first message to include the CREATE TABLE and INSERT statements.  That's very easy to miss, and also confusing.  It's much better to add a new reply with the new information.

               

              Here's one way to do the job:

              WITH    dayswanted    AS

              (

                  SELECT  TO_DATE ( '02.04.2017'   -- First day in output

                                  , 'DD.MM.YYYY'

                                  ) + LEVEL - 1  AS dt

                  ,       LEVEL                  AS daynum

                  FROM    dual

                  CONNECT BY  LEVEL  <= 10         -- Number of days wanted

              )

              ,    datatopivot    AS

              (

                  SELECT  d.daynum

                  ,       t.groupid, t.taskname

                  FROM             dayswanted  d

                  LEFT OUTER JOIN  tableinput  t  PARTITION BY (t.groupid)

                                                  ON   d.dt  >=  t.startdate

                                                  AND  d.dt  <   t.startdate + t.duration

              )

              SELECT    *

              FROM      datatopivot

              PIVOT     (    COUNT (taskname)

                        FOR  daynum  IN (  1,  2,  3,  4,  5

                                        ,  6,  7,  8,  9, 10

                                        )

                        )

              ORDER BY  groupid

              ;

              To change the first day in the output, you only need to change the TO_DATE statement, near the top.

              To change the number of days displayed, you need to change the sub-query dayswanted and also the PIVOT clause near the end.

               

              Ouptut (10 days, starting from April 2, 2017):

              GROUPID   1   2   3   4   5   6   7   8   9  10

              ------- --- --- --- --- --- --- --- --- --- ---

              1         0   2   2   2   2   2   2   2   3   3

              2         0   1   1   1   1   1   1   1   1   1

              If you want more meaningful  column headers like "04.02.2017", you need Dynamic SQL.

               

              Alternatively, you could add an extra row of output containing what looks like headers (e.g. '04.03.2017') as the first row of the result set.  See Re: Please Help in query

              • 4. Re: Calculate a Pivot with Start and Duration
                martin_wolz

                Hey Frank,

                My next Question will be better organized!

                Thanks a lot!

                 

                Best regards

                 

                Martin

                • 5. Re: Calculate a Pivot with Start and Duration
                  Etbin

                  Just for fun

                   

                  with

                  data as

                  (select 'Task1' tsk,'GroupA' grp,date '2017-04-03' start_date,10 duration from dual union all

                  select 'Task2','GroupA',date '2017-04-03',15 from dual union all

                  select 'Task3','GroupA',date '2017-04-10',5 from dual union all

                  select 'Task4','GroupB',date '2017-04-17',3 from dual union all

                  select 'Task5','GroupB',date '2017-04-03',13 from dual

                  ),

                  limits as

                  (select tsk,grp,start_date,duration,start_date + duration - 1 end_date,min(start_date) over () from_date,max(start_date + duration - 1) over () final_date

                     from data

                  ),

                  charter(tsk,grp,start_date,duration,end_date,from_date,final_date,step,date_bar) as

                  (select tsk,grp,start_date,duration,end_date,from_date,final_date,0,

                          '|' || case when final_date between start_date and end_date then to_char(final_date,'dd.mm.yyyy') else 'NULL' end || '|'

                     from limits

                  union all

                  select tsk,grp,start_date,duration,end_date,from_date,final_date,step + 1,

                          '|' || case when final_date - step - 1 between start_date and end_date then to_char(final_date - step - 1,'dd.mm.yyyy') else 'NULL' end || date_bar

                     from charter

                    where final_date - step - 1 >= from_date

                  )

                  select tsk,grp,start_date,duration,

                         nullif(substr(date_bar,instr(date_bar,'|',1,1) + 1,instr(date_bar,'|',1,2) - instr(date_bar,'|',1,1) - 1),'NULL') c1,

                         nullif(substr(date_bar,instr(date_bar,'|',1,2) + 1,instr(date_bar,'|',1,3) - instr(date_bar,'|',1,2) - 1),'NULL') c2,

                         nullif(substr(date_bar,instr(date_bar,'|',1,3) + 1,instr(date_bar,'|',1,4) - instr(date_bar,'|',1,3) - 1),'NULL') c3,

                         nullif(substr(date_bar,instr(date_bar,'|',1,4) + 1,instr(date_bar,'|',1,5) - instr(date_bar,'|',1,4) - 1),'NULL') c4,

                         nullif(substr(date_bar,instr(date_bar,'|',1,5) + 1,instr(date_bar,'|',1,6) - instr(date_bar,'|',1,5) - 1),'NULL') c5,

                         nullif(substr(date_bar,instr(date_bar,'|',1,6) + 1,instr(date_bar,'|',1,7) - instr(date_bar,'|',1,6) - 1),'NULL') c6,

                         nullif(substr(date_bar,instr(date_bar,'|',1,7) + 1,instr(date_bar,'|',1,8) - instr(date_bar,'|',1,7) - 1),'NULL') c7,

                         nullif(substr(date_bar,instr(date_bar,'|',1,8) + 1,instr(date_bar,'|',1,9) - instr(date_bar,'|',1,8) - 1),'NULL') c8,

                         nullif(substr(date_bar,instr(date_bar,'|',1,9) + 1,instr(date_bar,'|',1,10) - instr(date_bar,'|',1,9) - 1),'NULL') c9,

                         nullif(substr(date_bar,instr(date_bar,'|',1,10) + 1,instr(date_bar,'|',1,11) - instr(date_bar,'|',1,10) - 1),'NULL') c10,

                         nullif(substr(date_bar,instr(date_bar,'|',1,11) + 1,instr(date_bar,'|',1,12) - instr(date_bar,'|',1,11) - 1),'NULL') c11,

                         nullif(substr(date_bar,instr(date_bar,'|',1,12) + 1,instr(date_bar,'|',1,13) - instr(date_bar,'|',1,12) - 1),'NULL') c12,

                         nullif(substr(date_bar,instr(date_bar,'|',1,13) + 1,instr(date_bar,'|',1,14) - instr(date_bar,'|',1,13) - 1),'NULL') c13,

                         nullif(substr(date_bar,instr(date_bar,'|',1,14) + 1,instr(date_bar,'|',1,15) - instr(date_bar,'|',1,14) - 1),'NULL') c14,

                         nullif(substr(date_bar,instr(date_bar,'|',1,15) + 1,instr(date_bar,'|',1,16) - instr(date_bar,'|',1,15) - 1),'NULL') c15,

                         nullif(substr(date_bar,instr(date_bar,'|',1,16) + 1,instr(date_bar,'|',1,17) - instr(date_bar,'|',1,16) - 1),'NULL') c16,

                         nullif(substr(date_bar,instr(date_bar,'|',1,17) + 1,instr(date_bar,'|',1,18) - instr(date_bar,'|',1,17) - 1),'NULL') c17

                    from (select tsk,grp,start_date,duration,date_bar

                            from charter

                           where step = (select distinct final_date - from_date from limits)

                         )

                   

                   

                  TSKGRPSTART_DATEDURATIONC1C2C3C4C5C6C7C8C9C10C11C12C13C14C15C16C17
                  Task1GroupA03-APR-2017 00:00:001003.04.201704.04.201705.04.201706.04.201707.04.201708.04.201709.04.201710.04.201711.04.201712.04.2017-------
                  Task2GroupA03-APR-2017 00:00:001503.04.201704.04.201705.04.201706.04.201707.04.201708.04.201709.04.201710.04.201711.04.201712.04.201713.04.201714.04.201715.04.201716.04.201717.04.2017--
                  Task3GroupA10-APR-2017 00:00:005-------10.04.201711.04.201712.04.201713.04.201714.04.2017-----
                  Task4GroupB17-APR-2017 00:00:003--------------17.04.201718.04.201719.04.2017
                  Task5GroupB03-APR-2017 00:00:001303.04.201704.04.201705.04.201706.04.201707.04.201708.04.201709.04.201710.04.201711.04.201712.04.201713.04.201714.04.201715.04.2017----

                   

                   

                  Regards

                   

                  Etbin