2 Replies Latest reply: Jan 4, 2013 4:41 AM by Troll35 RSS

    Query Help

    fame
      Hi all,

      This is table structure,
      create table emp_leave_details
      (emp_key number,
       leave_type varchar2(10),
       from_date date,
       to_date date,
       days number);
      
      select * from emp_leave_details where emp_key = 100;
      Output :
      emp_key          leave_type      from_date      to_date    days
      --------------------
      100                    CL                15-03-2012       15-03-2012    1
      100                    SL                25-06-2012       26-06-2012    2
      100                    CL                30-10-2012       30-10-2012    1
      Wanted Output
      Month   1       2     3   4   5  6   7   8   9  10  11  12  13  14  15  16   17   18  19  20   21  22  23  24   25   26    27   28   29  30   31
      --------------
      JAN   
      
      FEB
      
      MAR                                                                               CL
      
      APR
      
      MAY
      
      JUN                                                                                                                                             SL  SL
      
      JUL
      
      AUG
      
      SEP
      
      OCT                                                                                                                                                                                     CL
      
      NOV
      
      DEC
      --------------------------



      I wanted days as column and months as row,
      if the employee takes the leave in 15 march means 15th date column of march row, mark as leave type.




      Regards,

      Fame

      Edited by: fame on 4 Jan, 2013 3:13 PM

      Edited by: fame on 4 Jan, 2013 3:15 PM

      Edited by: BluShadow on 04-Jan-2013 09:55
      added {noformat}
      {noformat} tags for readability.  You should know how to do this yourself by now: {message:id=9360002}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
        • 1. Re: Query Help
          jeneesh
          SQL> with all_dates as
            2  (
            3   select to_date('31122011','ddmmyyyy') + level dt
            4   from dual
            5   connect by to_date('31122011','ddmmyyyy') + level <=
            6      to_date('31122012','ddmmyyyy')
            7  ),
            8  leaves as
            9  (
           10   select to_char(dt,'MON') mth,
           11         to_char(dt,'dd') dy,
           12         decode(l.from_date,null,null,leave_type) status
           13   from all_dates d,emp_leave_details l
           14   where d.dt between l.from_date(+) and l.to_date(+)
           15  )
           16  select *
           17  from leaves
           18  pivot
           19   (
           20     max(status) for dy in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,
           21                            16,17,18,19,20,21,22,23,24,25,26,27,
           22                       28,29,30,31)
           23   )
           24  order by to_date(mth,'MON');
          
          MTH 1  2  3  4  5  6  7  8  9  10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
          --- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
          JAN
          FEB
          MAR                                           LV
          APR
          MAY
          JUN                                                                         LV LV LV
          JUL
          AUG
          SEP
          OCT                                                                                        LV
          NOV
          DEC
          
          12 rows selected.
          Edited by: jeneesh on Jan 4, 2013 3:26 PM
          • 2. Re: Query Help
            Troll35
            Hello,

            If you do not have Oracle 11, you can't use PIVOT function. You can try this :
            with emp_leave_details as(
            select 100 emp_key, 'CL' level_type, to_date('15-03-2012','DD-MM-YYYY') from_date , to_date('15-03-2012','DD-MM-YYYY') to_date, 1 days from dual union all
            select 100 emp_key, 'SL' level_type, to_date('25-06-2012','DD-MM-YYYY') from_date , to_date('26-06-2012','DD-MM-YYYY') to_date, 2 days from dual union all
            select 100 emp_key, 'CL' level_type, to_date('30-10-2012','DD-MM-YYYY') from_date , to_date('30-10-2012','DD-MM-YYYY') to_date, 1 days from dual
            ) ,
            all_dates as
                (
                 select to_date('31122011','ddmmyyyy') + level dt
                 from dual
                 connect by to_date('31122011','ddmmyyyy') + level <=
                    to_date('31122012','ddmmyyyy')
            ),
            leaves as
                (
                select to_char(dt,'MON') mth,
                        to_char(dt,'MM') mth_number,
                      to_char(dt,'dd') dy,
                      level_type
                from all_dates d,emp_leave_details l
                where d.dt between l.from_date(+) and l.to_date(+)
               )
            select mth,
            max(case when dy='01' then level_type else null end) "01",
            max(case when dy='02' then level_type else null end) "02",
            max(case when dy='03' then level_type else null end) "03",
            max(case when dy='04' then level_type else null end) "04",
            max(case when dy='05' then level_type else null end) "05",
            max(case when dy='06' then level_type else null end) "06",
            max(case when dy='07' then level_type else null end) "07",
            max(case when dy='08' then level_type else null end) "08",
            max(case when dy='09' then level_type else null end) "09",
            max(case when dy='10' then level_type else null end) "10",
            max(case when dy='11' then level_type else null end) "11",
            max(case when dy='12' then level_type else null end) "12",
            max(case when dy='13' then level_type else null end) "13",
            max(case when dy='14' then level_type else null end) "14",
            max(case when dy='15' then level_type else null end) "15",
            max(case when dy='16' then level_type else null end) "16",
            max(case when dy='17' then level_type else null end) "17",
            max(case when dy='18' then level_type else null end) "18",
            max(case when dy='19' then level_type else null end) "19",
            max(case when dy='20' then level_type else null end) "20",
            max(case when dy='21' then level_type else null end) "21",
            max(case when dy='22' then level_type else null end) "22",
            max(case when dy='23' then level_type else null end) "23",
            max(case when dy='24' then level_type else null end) "24",
            max(case when dy='25' then level_type else null end) "25",
            max(case when dy='26' then level_type else null end) "26",
            max(case when dy='27' then level_type else null end) "27",
            max(case when dy='28' then level_type else null end) "28",
            max(case when dy='29' then level_type else null end) "29",
            max(case when dy='30' then level_type else null end) "30",
            max(case when dy='31' then level_type else null end) "31"
            from leaves
            group by mth, mth_number
            order by mth_number;
            
            TH          01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31                                             
            ------------ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --                                             
            JAN                                                                                                                                                   
            FEB                                                                                                                                                   
            MAR                                                    CL                                                                                             
            APR                                                                                                                                                   
            MAY                                                                                                                                                   
            JUN                                                                                  SL SL                                                            
            JUL                                                                                                                                                   
            AUG                                                                                                                                                   
            SEP                                                                                                                                                   
            OCT                                                                                                 CL                                                
            NOV                                                                                                                                                   
            DEC                                                                                                                 
            Hope it will help.

            Regards.