0 Replies Latest reply: Nov 23, 2012 5:05 AM by 944789 RSS

    chanllenging matrix output

    944789
      i have one table where we record the 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
      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.
      The table and inserts are as below.
      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.
      i am getting the output as below but i want to display the data in reports 6i in matrix format as below and take it to
      excel using rpt2xls tool.
      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;
      
      --Current output is
      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                  
      
      --If you see the normal hours of jt_no 4 as 2 but it has to be 2.50, the desired output is as follows
      
                1/11/2012  04/11/2012 09/11/2012  14/11/2012 15/11/2012               
       AA            8         8
                       4         2 
       BB                                             0
                                                        7
       
       CC                                                                 2.5
                                                                             0
       DD                                                                                   2
                                                                                              0