OraFAQ Forum: SQL & PL/SQL » complex hours breakdown query

Home » SQL & PL/SQL » SQL & PL/SQL » complex hours breakdown query (Oracle 10g)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Switch to threaded view of this topic

Create a new topic

Submit Reply

complex hours breakdown query [message #571272]

Thu, 22 November 2012 17:04

arif_md2009
Messages: 677
Registered: May 2009
Location: United Arab Emirates

Senior Member

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
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.


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;

SELECT   jt_txn, jt_no, jt_emp_code, jt_duration, jt_dt, TO_CHAR (jt_dt, 'D'),
DECODE (NVL(TO_NUMBER((DECODE(TO_CHAR(JT_DT,'D'),6,0,
(DECODE(SIGN(SUM(JT_DURATION)-8),1,8,-1,SUM(JT_DURATION))),
(DECODE(SIGN(SUM(JT_DURATION)-8),1,8,-1,SUM(JT_DURATION)))))),0)-
(TRUNC(TO_NUMBER(DECODE(TO_CHAR(JT_DT,'D'),6,0
,(DECODE(SIGN(SUM(JT_DURATION)-8),1,8,-1,SUM(JT_DURATION),0,8)),
 (DECODE(SIGN(SUM(JT_DURATION)-8),1,8,-1,SUM(JT_DURATION),0,8)))))) ,0,
1,2)AA
  FROM ot_job_det
GROUP BY jt_txn, jt_no, jt_emp_code, jt_dt, jt_duration, TO_CHAR (jt_dt, 'D')

Report message to a moderator

Send a private message to this user

Re: complex hours breakdown query [message #571294 is a reply to message #571272]

Fri, 23 November 2012 01:27

Michel Cadot
Messages: 54125
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

1/ Is your test case complete enough to fulfil all the cases?
2/ Show the output you want for the data you give
3/ '15-Nov-2012' si a string NOT a date (how many times did we tell you so? 100? It would be not so far from this number)

SQL> select to_date('15-Nov-2012') from dual;
select to_date('15-Nov-2012') from dual
               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

Regards
Michel

Report message to a moderator

Send a private message to this user

Re: complex hours breakdown query [message #571312 is a reply to message #571294]

Fri, 23 November 2012 04:11

saipradyumn
Messages: 131
Registered: October 2011
Location: Hyderabad

Senior Member

Hi Following is Query to our requirement

select case
         when jt_duration > 8 and
              trim(to_char(det.jt_dt, 'Day')) <> 'Friday' then
          8
         when jt_duration > 0 and
              trim(trim(to_char(det.jt_dt, 'Day'))) = 'Friday' then
          0
         when jt_duration < 8 and
              trim(to_char(det.jt_dt, 'Day')) <> 'Friday' then
          round(jt_duration)
       end normal,
       case
         when jt_duration > 8 and
              trim(to_char(det.jt_dt, 'Day')) <> 'Friday' then
          jt_duration - 8
         when jt_duration > 0 and
              trim(trim(to_char(det.jt_dt, 'Day'))) = 'Friday' then
          jt_duration
         when jt_duration < 8 and
              trim(to_char(det.jt_dt, 'Day')) <> 'Friday' then
          0
       end ot_hours,
       to_char(det.jt_dt, 'Day') jt_day,
       det.*
  from ot_job_det det;

Please let me know it status

Sai Pradyumn

Report message to a moderator

Send a private message to this user

Re: complex hours breakdown query [message #571315 is a reply to message #571312]

Fri, 23 November 2012 04:44

arif_md2009
Messages: 677
Registered: May 2009
Location: United Arab Emirates

Senior Member

thanks sai and Michel for prompt response and help , the result is almost there, but with only one requirement pending, if the duration is less that 8 on any day with decimals , for example if its 2.43 or 2.30 then it has to be 2.50 , actually what i am doing is by checking like this
decode(jt_duration-trunc(jt_duration),0,trunc(jt_duration)+0.5,jt_duration),i am checking if the jt_duration is having any decimals , if so then truncating it and adding the 0.5 directly, another thing is can we have a pivot like output , as the requirement is to have a report in pivot format or matrix like output given below.


--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

Report message to a moderator

Send a private message to this user

Re: complex hours breakdown query [message #571316 is a reply to message #571315]

Fri, 23 November 2012 04:51

Michel Cadot
Messages: 54125
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Michel Cadot wrote on Fri, 23 November 2012 08:27

1/ Is your test case complete enough to fulfil all the cases?
2/ Show the output you want for the data you give
3/ '15-Nov-2012' si a string NOT a date (how many times did we tell you so? 100? It would be not so far from this number)
SQL> select to_date('15-Nov-2012') from dual;
select to_date('15-Nov-2012') from dual
               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
Regards
Michel

Report message to a moderator

Send a private message to this user

Re: complex hours breakdown query [message #571319 is a reply to message #571316]

Fri, 23 November 2012 05:09

arif_md2009
Messages: 677
Registered: May 2009
Location: United Arab Emirates

Senior Member

hi michel , the test case is complete , i dont know why you are using to_date, as you see the execution is done.

Report message to a moderator

Send a private message to this user

Re: complex hours breakdown query [message #571323 is a reply to message #571319]

Fri, 23 November 2012 05:54

Michel Cadot
Messages: 54125
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

I use to_date to show you that what you provided is NOT a date.
If you prefer:

SQL> insert into ot_job_det values ( 'time',1,'04-Nov-2012','AA',10);
insert into ot_job_det values ( 'time',1,'04-Nov-2012','AA',10)
                                         *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

And for the THIRD time:

Quote:

2/ Show the output you want for the data you give

After more than 500 posts you still don't how you MUST post!

Regards
Michel

Report message to a moderator

Send a private message to this user

Re: complex hours breakdown query [message #571330 is a reply to message #571323]

Fri, 23 November 2012 07:06

saipradyumn
Messages: 131
Registered: October 2011
Location: Hyderabad

Senior Member

Hi Query For required output


Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as dbo
 
SQL> 
SQL> with data as
  2   (select case
  3             when jt_duration > 8 and
  4                  trim(to_char(det.jt_dt, 'Day')) <> 'Friday' then
  5              8
  6             when jt_duration > 0 and
  7                  trim(trim(to_char(det.jt_dt, 'Day'))) = 'Friday' then
  8              0
  9             when jt_duration < 8 and
 10                  trim(to_char(det.jt_dt, 'Day')) <> 'Friday' then
 11              ROUND(jt_duration, 1)
 12           end normal_H,
 13           case
 14             when jt_duration > 8 and
 15                  trim(to_char(det.jt_dt, 'Day')) <> 'Friday' then
 16              jt_duration - 8
 17             when jt_duration > 0 and
 18                  trim(trim(to_char(det.jt_dt, 'Day'))) = 'Friday' then
 19              jt_duration
 20             when jt_duration < 8 and
 21                  trim(to_char(det.jt_dt, 'Day')) <> 'Friday' then
 22              0
 23           end ot_h,
 24           to_char(det.jt_dt, 'Day') jt_day,
 25           TO_NUMBER(TO_CHAR(DET.JT_DT, 'YYYYMMDD')) N_FORMATE_DATE,
 26           det.*
 27      from ot_job_det det),
 28  DATA_OT AS
 29   (select *
 30      from (select jt_emp_code,
 31                   N_FORMATE_DATE,
 32                   sum(OT_H) OT_H,
 33                   'OT' HOURSE_INDICATOR
 34              from data
 35             group by jt_emp_Code, N_FORMATE_DATE) pivot(sum(OT_H) for N_FORMATE_DATE in(20121101,
 36                                                                                         20121104,
 37                                                                                         20121109,
 38                                                                                         20121114,
 39                                                                                         20121115))),
 40  DATA_NORMAL AS
 41   (select *
 42      from (select jt_emp_code,
 43                   N_FORMATE_DATE,
 44                   sum(NORMAL_H) NORMAL_H,
 45                   'NORMAL' HOURSE_INDICATOR
 46              from data
 47             group by jt_emp_Code, N_FORMATE_DATE) pivot(sum(NORMAL_H) for N_FORMATE_DATE in(20121101,
 48                                                                                             20121104,
 49                                                                                             20121109,
 50                                                                                             20121114,                                                                                           20121115)))
 51  SELECT *
 52    FROM DATA_NORMAL
 53  UNION
 54  SELECT * FROM DATA_OT ORDER BY 1;
 
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.4 
CC          OT                                                         0 
DD          NORMAL                                                                2
DD          OT                                                                    0
 
8 rows selected
 
SQL>

Report message to a moderator

Send a private message to this user

Re: complex hours breakdown query [message #571331 is a reply to message #571330]

Fri, 23 November 2012 07:14

saipradyumn
Messages: 131
Registered: October 2011
Location: Hyderabad

Senior Member

Hi Arif,

Above query will work in Oracle 11g only,Please let me know if you are working on earlier versions.

Thanks
SaiPradyumn

Report message to a moderator

Send a private message to this user

Re: complex hours breakdown query [message #571332 is a reply to message #571330]

Fri, 23 November 2012 07:27

Michel Cadot
Messages: 54125
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

Query For required output

Which required output?

Regards
Michel

Report message to a moderator

Send a private message to this user

Re: complex hours breakdown query [message #571348 is a reply to message #571331]

Fri, 23 November 2012 21:41

arif_md2009
Messages: 677
Registered: May 2009
Location: United Arab Emirates

Senior Member

thanks sai, yes i am working on oracle 10.2.0.4 version and you know what the dates will not be fixed it will be as from date to date, and the latest query we need to check with oracle 10g version.

Report message to a moderator

Send a private message to this user

Re: complex hours breakdown query [message #571352 is a reply to message #571332]

Sat, 24 November 2012 03:33

arif_md2009
Messages: 677
Registered: May 2009
Location: United Arab Emirates

Senior Member

michel please check below the output.


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.4 
CC          OT                                                         0 
DD          NORMAL                                                                2
DD          OT                                                                    0

Report message to a moderator

Send a private message to this user

Re: complex hours breakdown query [message #571353 is a reply to message #571352]

Sat, 24 November 2012 03:36

Michel Cadot
Messages: 54125
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Too late, I'm tired to ask and ask and ask the same thing, now I don't want to investigate on this topic.

Regards
Michel

Report message to a moderator

Send a private message to this user

Re: complex hours breakdown query [message #571354 is a reply to message #571353]

Sat, 24 November 2012 03:37

arif_md2009
Messages: 677
Registered: May 2009
Location: United Arab Emirates

Senior Member

Michel , i am really sorry, kindly do check and help me.

Report message to a moderator

Send a private message to this user

Re: complex hours breakdown query [message #571363 is a reply to message #571331]

Sat, 24 November 2012 10:36

arif_md2009
Messages: 677
Registered: May 2009
Location: United Arab Emirates

Senior Member

hi sai, can we do the same thing using oracle 10g.

Report message to a moderator

Send a private message to this user

Re: complex hours breakdown query [message #571425 is a reply to message #571353]

Sun, 25 November 2012 22:07

arif_md2009
Messages: 677
Registered: May 2009
Location: United Arab Emirates

Senior Member

Thanks michel , i managed to do this requirement using Reports 6i (matrix report) and then using rpt2xls i managed to take it into excel with both normal and overtime together as below, the exact format like normal hours on top and ot below was not possible using oracle 10g as i see on this forum nobody has solution to this problem.


	1	2	3	4	6	7	8	9	10	11	13	14	15	16	17	18	19	20	21	22	23	24	25	28	29	30	31
R2212	8/2	8/2	8/2	8/2	8/2	8/2	8/2	8/2	8/2	8/2	8/2	8/2	8/2	8/2	8/2	8/2		8/2	8/2	8/2	8/2	8/2	0/10	8/2	8/2	8/2	8/2
R2213	8/2	8/2	8/2	8/2	8/2	8/2	8/2	8/2	8/2	8/0	8/0	8/0	8/0	8/0	8/0	8/0		8/0	8/0	8/0	8/0	8/2	0/10	8/2	8/2	8/2	8/2
R2214	8/2	8/2	8/2	8/2	8/2	8/2	8/2	8/2	8/2	8/2	8/2	8/2	8/2	8/2	8/2	8/2		8/2	8/2	8/2	8/2	8/2		8/2	8/2	8/2	8/2
R2215	8/2	8/2	8/2	8/2	8/2	8/2	8/2	8/2	8/2	8/2	8/2	8/2	8/2	8/2	8/2	8/2	0/10	8/2	8/2	8/2	8/2	8/2	0/8	8/2	8/2	8/2	8/2
R2216	8/2	8/2	8/2	8/2	8/2	8/2	8/2	8/2	8/2	8/2	8/2	8/2	8/4	8/2	8/3	8/2		8/2	8/2	8/4	8/2	8/2		8/3	8/2	8/3	8/3


Employee numbers as rows,dates for the month as columns and normal time and ot as value combined.

Report message to a moderator

Send a private message to this user

Re: complex hours breakdown query [message #571442 is a reply to message #571425]

Mon, 26 November 2012 01:23

saipradyumn
Messages: 131
Registered: October 2011
Location: Hyderabad

Senior Member

Hi Arif ,

Same query in Oracle 10G

SQL> 
SQL> with data as
  2   (select case
  3             when jt_duration > 8 and
  4                  trim(to_char(det.jt_dt, 'Day')) <> 'Friday' then
  5              8
  6             when jt_duration > 0 and
  7                  trim(trim(to_char(det.jt_dt, 'Day'))) = 'Friday' then
  8              0
  9             when jt_duration < 8 and
 10                  trim(to_char(det.jt_dt, 'Day')) <> 'Friday' then
 11              ROUND(jt_duration, 1)
 12           end normal_H,
 13           case
 14             when jt_duration > 8 and
 15                  trim(to_char(det.jt_dt, 'Day')) <> 'Friday' then
 16              jt_duration - 8
 17             when jt_duration > 0 and
 18                  trim(trim(to_char(det.jt_dt, 'Day'))) = 'Friday' then
 19              jt_duration
 20             when jt_duration < 8 and
 21                  trim(to_char(det.jt_dt, 'Day')) <> 'Friday' then
 22              0
 23           end ot_h,
 24           to_char(det.jt_dt, 'Day') jt_day,
 25           TO_NUMBER(TO_CHAR(DET.JT_DT, 'YYYYMMDD')) N_FORMATE_DATE,
 26           det.*
 27      from ot_job_det det),
 28  data_normal as
 29   (select DISTINCT jt_emp_code,
 30                    'Normal Hourses' Indicator,
 31                    SUM(DECODE(JT_DT, TO_DATE('01-NOV-2012'), NORMAL_H)) OVER(PARTITION BY JT_EMP_CODE) as "Day_11/01/2012",
 32                    SUM(DECODE(JT_DT, TO_DATE('04-NOV-2012'), NORMAL_H)) OVER(PARTITION BY JT_EMP_CODE) as "Day_11/04/2012",
 33                    SUM(DECODE(JT_DT, TO_DATE('09-NOV-2012'), NORMAL_H)) OVER(PARTITION BY JT_EMP_CODE) as "Day_11/09/2012",
 34                    SUM(DECODE(JT_DT, TO_DATE('14-NOV-2012'), NORMAL_H)) OVER(PARTITION BY JT_EMP_CODE) as "Day_11/14/2012",
 35                    SUM(DECODE(JT_DT, TO_DATE('15-NOV-2012'), NORMAL_H)) OVER(PARTITION BY JT_EMP_CODE) as "Day_11/15/2012"
 36      from data) ,
 37  data_ot as
 38   (select DISTINCT jt_emp_code,
 39                    'OT Hourses' Indicator,
 40                    SUM(DECODE(JT_DT, TO_DATE('01-NOV-2012'), OT_H)) OVER(PARTITION BY JT_EMP_CODE) as "Day_11/01/2012",
 41                    SUM(DECODE(JT_DT, TO_DATE('04-NOV-2012'), OT_H)) OVER(PARTITION BY JT_EMP_CODE) as "Day_11/04/2012",
 42                    SUM(DECODE(JT_DT, TO_DATE('09-NOV-2012'), OT_H)) OVER(PARTITION BY JT_EMP_CODE) as "Day_11/09/2012",
 43                    SUM(DECODE(JT_DT, TO_DATE('14-NOV-2012'), OT_H)) OVER(PARTITION BY JT_EMP_CODE) as "Day_11/14/2012",
 44                    SUM(DECODE(JT_DT, TO_DATE('15-NOV-2012'), OT_H)) OVER(PARTITION BY JT_EMP_CODE) as "Day_11/15/2012"
 45      from data)
 46  select * from data_normal
 47  UNION
 48  select * from data_OT
 49  ORDER BY 1
 50  ;
 
JT_EMP_CODE INDICATOR      Day_11/01/2012 Day_11/04/2012 Day_11/09/2012 Day_11/14/2012 Day_11/15/2012
----------- -------------- -------------- -------------- -------------- -------------- --------------
AA          Normal Hourses              8              8                               
AA          OT Hourses                  4              2                               
BB          Normal Hourses                                            0                
BB          OT Hourses                                                7                
CC          Normal Hourses                                                         2.4 
CC          OT Hourses                                                               0 
DD          Normal Hourses                                                                          2
DD          OT Hourses                                                                              0
 
8 rows selected
 
SQL>

Thanks
SaiPradyumn

Report message to a moderator

Send a private message to this user

Re: complex hours breakdown query [message #571460 is a reply to message #571442]

Mon, 26 November 2012 03:39

arif_md2009
Messages: 677
Registered: May 2009
Location: United Arab Emirates

Senior Member

Hi sai, thanks for the response, i will pass the from date and to date as parameters , they are not static, i mean i can give the start of the month date till end of the month date or i will by running reports for month , in which all the days will be displayed as column.

Report message to a moderator

Send a private message to this user

Re: complex hours breakdown query [message #571576 is a reply to message #571460]

Tue, 27 November 2012 04:14

saipradyumn
Messages: 131
Registered: October 2011
Location: Hyderabad

Senior Member

Hi Arif,

Irrespective of the dates in the table , if you execute the query it will give the current month report
based on working hours per day for each employee.

with data as
 (select case
           when jt_duration > 8 and
                trim(to_char(det.jt_dt, 'Day')) <> 'Friday' then
            8
           when jt_duration > 0 and
                trim(trim(to_char(det.jt_dt, 'Day'))) = 'Friday' then
            0
           when jt_duration < 8 and
                trim(to_char(det.jt_dt, 'Day')) <> 'Friday' then
            ROUND(jt_duration, 1)
         end normal_H,
         case
           when jt_duration > 8 and
                trim(to_char(det.jt_dt, 'Day')) <> 'Friday' then
            jt_duration - 8
           when jt_duration > 0 and
                trim(trim(to_char(det.jt_dt, 'Day'))) = 'Friday' then
            jt_duration
           when jt_duration < 8 and
                trim(to_char(det.jt_dt, 'Day')) <> 'Friday' then
            0
         end ot_h,
         to_char(det.jt_dt, 'Day') jt_day,
         TO_NUMBER(TO_CHAR(DET.JT_DT, 'YYYYMMDD')) N_FORMATE_DATE,
         det.*
    from ot_job_det det  WHERE JT_DT BETWEEN  TRUNC(SYSDATE, 'MM')AND LAST_DAY(SYSDATE) )
 select  DISTINCT jt_emp_code,
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM'),'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM'),'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 1",
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+1,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+1,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 2",
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+2,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+2,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 3", 
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+3,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+3,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 4",
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+4,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+4,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 5",
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+5,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+5,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 6", 
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+6,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+6,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 7",
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+7,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+7,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 8",
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+8,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+8,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 9", 
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+9,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+9,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 10" ,                  
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+10,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+10,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 11",
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+11,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+11,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 12",
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+12,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+12,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 13", 
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+13,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+13,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 14",
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+14,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+14,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 15",
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+15,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+15,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 16", 
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+16,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+16,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 17",
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+17,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+17,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 18",
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+18,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+18,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 19", 
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+19,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+19,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 20" , 
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+20,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+20,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 21",
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+21,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+21,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 22",
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+22,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+22,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 23", 
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+23,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+23,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 24",
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+24,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+24,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 25",
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+25,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+25,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 26", 
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+26,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+26,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 27",
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+27,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+27,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 28",
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+28,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+28,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 29", 
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+29,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+29,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 30", 
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+30,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                 SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+30,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 31"
    from data ORDER BY 1;

At max one month contains 31 days.So I had derived 31 columns,if the data is not there for month end dates query will give the result as 0/0.

Please let me know the status.

Thanks
SaiPradyumn

Report message to a moderator

Send a private message to this user

Re: complex hours breakdown query [message #571582 is a reply to message #571576]

Tue, 27 November 2012 05:38

arif_md2009
Messages: 677
Registered: May 2009
Location: United Arab Emirates

Senior Member

Dear Sai,

Many many thanks for getting this output as desired, i changed your query a bit since i was not able to run it oracle sql old client , and one condition is missing that incase the duration is in decimals lile 2.34 or 4.35 then it has to be rounded of to 2.50 and 4.5 respectively , there are three more criterias/columns per each employee to be added
Criteria for normal hours
a)summary of all the normal hours 8 or less except on friday. -Total Normal Hours
b)Overtime greater than 2 should be recorded seperately, for example there is 12 and 13 hours jt_duration , then out of 12 8 will go as normal, 2 will go as ot1 and another 2 will go as ot2, for 13 - 8 nhrs,2ot1 ,3 ot2. OT1 AND OT2
c)Fridays should be seperate overtime.--OT3


select  DISTINCT jt_emp_code,
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM'),'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM'),'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 1",
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+1,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+1,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 2",
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+2,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+2,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 3", 
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+3,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+3,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 4",
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+4,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+4,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 5",
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+5,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+5,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 6", 
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+6,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+6,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 7",
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+7,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+7,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 8",
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+8,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+8,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 9", 
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+9,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+9,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 10" ,                  
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+10,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+10,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 11",
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+11,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+11,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 12",
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+12,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+12,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 13", 
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+13,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+13,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 14",
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+14,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+14,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 15",
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+15,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+15,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 16", 
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+16,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+16,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 17",
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+17,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+17,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 18",
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+18,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+18,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 19", 
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+19,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+19,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 20" , 
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+20,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+20,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 21",
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+21,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+21,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 22",
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+22,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+22,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 23", 
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+23,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+23,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 24",
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+24,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+24,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 25",
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+25,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+25,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 26", 
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+26,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+26,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 27",
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+27,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+27,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 28",
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+28,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+28,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 29", 
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+29,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+29,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 30", 
                  SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+30,'YYYYMMDD'), NORMAL_H,0)) OVER(PARTITION BY JT_EMP_CODE) ||'/' ||
                 SUM(DECODE(TO_CHAR(JT_DT,'YYYYMMDD'),TO_CHAR(TRUNC(SYSDATE,'MM')+30,'YYYYMMDD'), OT_H,0)) OVER(PARTITION BY JT_EMP_CODE)   as "Day 31"
    from (
 select case
           when jt_duration > 8 and
                trim(to_char(jt_dt, 'Day')) <> 'Friday' then
            8
           when jt_duration > 0 and
                trim(trim(to_char(jt_dt, 'Day'))) = 'Friday' then
            0
           when jt_duration < 8 and
                trim(to_char(jt_dt, 'Day')) <> 'Friday' then
            ROUND(jt_duration, 1)
         end normal_H,
         case
           when jt_duration > 8 and
                trim(to_char(jt_dt, 'Day')) <> 'Friday' then
            jt_duration - 8
           when jt_duration > 0 and
                trim(trim(to_char(jt_dt, 'Day'))) = 'Friday' then
            jt_duration
           when jt_duration < 8 and
                trim(to_char(jt_dt, 'Day')) <> 'Friday' then
            0
         end ot_h,
         to_char(jt_dt, 'Day') jt_day,
         TO_NUMBER(TO_CHAR(JT_DT, 'YYYYMMDD')) N_FORMATE_DATE,JT_EMP_CODE,JT_DT
           from ot_job_det   WHERE JT_DT BETWEEN  TRUNC(SYSDATE, 'MM')AND LAST_DAY(SYSDATE))
           data ORDER BY 1;

Report message to a moderator

Send a private message to this user

Switch to threaded view of this topic

Create a new topic

Submit Reply

Previous Topic:	Masking in Oracle
Next Topic:	Select query with rownum=1 how to use order by

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 18 11:20:01 CDT 2013

Total time taken to generate the page: 7.29944 seconds