6 Replies Latest reply: Nov 25, 2012 11:24 AM by rp0428 RSS

    matrix like output required

    944789
      i have one table where we record th e daily manhours for all the workers, the data will be entered as total duration, out of which i need to extract the normal hours worked and overtime hours seperately, there are certain rules for extraction as , i want the output in pivot format.
      a) The Normal hours are only 8 hours per day , if the total duration is for 10 hours then normal hours will be 8 and overtime will be 2 if its on any day other than friday.
      b) If the total duration is 12 hours then 8 will still go as normal and 4 will go as overtime hours.
      c) we have to consider the entire duration as overtime hours if its on friday
      d) Normal hours column will be 0 on friday.
      e) if the duration is less than 8 and its not on friday , then it will be considered as normal hours
      f) If the duration is comming as 2.43 or 3.30 then it should be rounded off as 2.50 and 3.50.

      The table and inserts are as below.
      create table ot_job_det (jt_txn varchar2(12),jt_no number,jt_dt date ,jt_emp_code varchar2(6),jt_duration number )
      
      insert into ot_job_det values ( 'time',1,'04-Nov-2012','AA',10);
      
      insert into ot_job_det values ('time',2,'01-Nov-2012','AA',12);
      
      insert into ot_job_det values ('time',3,'09-Nov-2012','BB',7);
      
      insert into ot_job_det values ('time',4,'14-Nov-2012','CC',2.43);
      
      insert into ot_job_det values ('time',5,'15-Nov-2012','DD',2);
      
      COMMIT;
      
      SQL> select case
        2           when jt_duration > 8 and
        3                trim(to_char(det.jt_dt, 'Day')) <> 'Friday' then
        4            8
        5           when jt_duration > 0 and
        6                trim(trim(to_char(det.jt_dt, 'Day'))) = 'Friday' then
        7            0
        8           when jt_duration < 8 and
        9                trim(to_char(det.jt_dt, 'Day')) <> 'Friday' then
       10            round(jt_duration)
       11         end normal,
       12         case
       13           when jt_duration > 8 and
       14                trim(to_char(det.jt_dt, 'Day')) <> 'Friday' then
       15            jt_duration - 8
       16           when jt_duration > 0 and
       17                trim(trim(to_char(det.jt_dt, 'Day'))) = 'Friday' then
       18            jt_duration
       19           when jt_duration < 8 and
       20                trim(to_char(det.jt_dt, 'Day')) <> 'Friday' then
       21            0
       22         end ot_hours,
       23         to_char(det.jt_dt, 'Day') jt_day,
       24         det.*
       25    from ot_job_det det;
      
      /
      
      
      
      NORMAL  OT_HOURS JT_DAY    JT_TXN           JT_NO JT_DT      JT_EMP JT_DURATION                     
      ------ --------- --------- ------------ --------- ---------- ------ -----------                     
           8         2 Sunday    time                 1 04/11/2012 AA              10                     
           8         4 Thursday  time                 2 01/11/2012 AA              12                     
           0         7 Friday    time                 3 09/11/2012 BB               7                     
           2         0 Wednesday time                 4 14/11/2012 CC            2.43                     
           2         0 Thursday  time                 5 15/11/2012 DD               2    
      
      
      
      
      i want the output in this format pivot 
      
      
      JT_EMP_CODE HOURSE_INDICATOR   20121101   20121104   20121109   20121114   20121115
      
      AA                   NORMAL                        8               8                       
      AA                     OT                             4                2                       
      BB                  NORMAL                                                           0            
      BB                     OT                                                                7            
      CC                  NORMAL                                                                            2.5 
      CC                    OT                                                                                   0 
      DD                NORMAL                                                                                                 2
      DD                   OT                                                                                                      0