Forum Stats

  • 3,757,253 Users
  • 2,251,215 Discussions
  • 7,869,779 Comments

Discussions

Need Day wise leave statistic report

I am trying to develop new day wise leave statistic report, as attachment , please check below code and suggest.


CREATE TABLE GET_DATES (DATE_START DATE,DATE_END DATE,PERSON_ID NUMBER);

 INSERT INTO GET_DATES

VALUES (TO_DATE('11-MAY-2014'), TO_DATE('04-JUN-2014'),26876);

 

INSERT INTO GET_DATES

VALUES (TO_DATE('22-JAN-2017'), TO_DATE('22-MAR-2017'),26876);

 

COMMIT;

 

1. select date_start, date_end, date_end - date_start +1 days FROM GET_DATES;

 the below query is working fine for one row, if more than 1 row its records are more than million.

but the desired output is to get correct days as attached file.


 2. Select level - 1 lvl , date_start, date_end

 FROM

(select date_start, date_end FROM GET_DATES

WHERE ROWNUM = 1)

connect by  level <= (date_end - date_start)+ 1

  

   3. Final query

  

   select * from(

  select date_start + lvl date_,

  Trim(to_char(date_start + lvl, 'Day')) current_day_,

  to_char(date_start + lvl, 'mm') month_,

  to_char(date_start + lvl, 'YYYY') Year_

   from (

  Select level - 1 lvl , date_start, date_end

 FROM

(select date_start, date_end FROM GET_DATES

WHERE ROWNUM = 1)

connect by

  level <= (date_end - date_start)+ 1))

  pivot (count(date_)date_ for current_day_ in

('Sunday' Sun,'Monday' Mon ,'Tuesday' Tue,'Wednesday' Wed,'Thursday' Thu,'Friday' Fri,'Saturday' Sat))

order by 2, 1;


Thank you in advance

Best Answers

  • Shaik_Muhammad
    Shaik_Muhammad Member Posts: 6 Red Ribbon
    Accepted Answer

    Yes Sir, you suggested me to post in this forum, ..

    1) This forum is for discussion of the use of the SQL Developer GUI tool. Your question is purely a SQL question. Better asked at https://community.oracle.com/tech/developers/categories/3063-general_questions1

    Am testing code suggested by Mr. Soloman..

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,722 Black Diamond
    Accepted Answer

    Next time provide version upfront, expecially when you are on very old version:

    with t as (
               select  date_start + level - 1 dt
                 from  get_dates
                 connect by rowid = prior rowid
                        and level <= date_end - date_start + 1
                        and prior sys_guid() is not null
              )
    select  case grouping(to_char(dt,'mm')) when 1 then to_char(dt,'yyyy') || ' Total' end year,
            to_char(dt,'mm') month,
            sum(case dt - trunc(dt,'iw') when 5 then 1 end) saturday,
            sum(case dt - trunc(dt,'iw') when 6 then 1 end) sunday,
            sum(case dt - trunc(dt,'iw') when 0 then 1 end) monday,
            sum(case dt - trunc(dt,'iw') when 1 then 1 end) tuesday,
            sum(case dt - trunc(dt,'iw') when 2 then 1 end) wednesday,
            sum(case dt - trunc(dt,'iw') when 3 then 1 end) thursday,
            sum(case dt - trunc(dt,'iw') when 4 then 1 end) friday,
            count(*) grand_total
      from  t
      group by grouping sets((to_char(dt,'yyyy')),(to_char(dt,'yyyy'),to_char(dt,'mm')))
      order by to_char(dt,'yyyy'),
               to_char(dt,'mm')
    /
    
    
    YEAR       MONTH   SATURDAY     SUNDAY     MONDAY    TUESDAY  WEDNESDAY   THURSDAY     FRIDAY GRAND_TOTAL
    ---------- ----- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -----------
               05             3          3          3          3          3          3          3          21
               06                        1          1          1          1                                 4
    2014 Total                3          4          4          4          4          3          3          25
               01             1          2          2          2          1          1          1          10
               02             4          4          4          4          4          4          4          28
               03             3          3          3          3          4          3          3          22
    2017 Total                8          9          9          9          9          8          8          60
    
    
    7 rows selected.
    
    
    SQL>
    
    

    SY.

    Shaik_Muhammad

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,722 Black Diamond
    with t as (
               select  date_start + offset dt
                 from  get_dates,
                       lateral(
                               select  level - 1 offset
                                 from  dual
                                 connect by level <= date_end - date_start + 1
                              )
              )
    select  case grouping(to_char(dt,'mm')) when 1 then to_char(dt,'yyyy') || ' Total' end year,
            to_char(dt,'mm') month,
            sum(case dt - trunc(dt,'iw') when 5 then 1 end) saturday,
            sum(case dt - trunc(dt,'iw') when 6 then 1 end) sunday,
            sum(case dt - trunc(dt,'iw') when 0 then 1 end) monday,
            sum(case dt - trunc(dt,'iw') when 1 then 1 end) tuesday,
            sum(case dt - trunc(dt,'iw') when 2 then 1 end) wednesday,
            sum(case dt - trunc(dt,'iw') when 3 then 1 end) thursday,
            sum(case dt - trunc(dt,'iw') when 4 then 1 end) friday,
            count(*) grand_total
      from  t
      group by grouping sets((to_char(dt,'yyyy')),(to_char(dt,'yyyy'),to_char(dt,'mm')))
      order by to_char(dt,'yyyy'),
               to_char(dt,'mm')
    /
    
    YEAR       MO   SATURDAY     SUNDAY     MONDAY    TUESDAY  WEDNESDAY   THURSDAY     FRIDAY GRAND_TOTAL
    ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -----------
               05          3          3          3          3          3          3          3          21
               06                     1          1          1          1                                 4
    2014 Total             3          4          4          4          4          3          3          25
               01          1          2          2          2          1          1          1          10
               02          4          4          4          4          4          4          4          28
               03          3          3          3          3          4          3          3          22
    2017 Total             8          9          9          9          9          8          8          60
    
    
    7 rows selected.
    
    
    SQL>
    

    SY.

  • Shaik_Muhammad
    Shaik_Muhammad Member Posts: 6 Red Ribbon
    Accepted Answer

    Yes Sir, you suggested me to post in this forum, ..

    1) This forum is for discussion of the use of the SQL Developer GUI tool. Your question is purely a SQL question. Better asked at https://community.oracle.com/tech/developers/categories/3063-general_questions1

    Am testing code suggested by Mr. Soloman..

  • EdStevens
    EdStevens Member Posts: 28,453 Gold Crown

    Yes Sir, you suggested me to post in this forum, ..

    Ah, so I did. Mea culpa.

  • Shaik_Muhammad
    Shaik_Muhammad Member Posts: 6 Red Ribbon
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,722 Black Diamond
    Accepted Answer

    Next time provide version upfront, expecially when you are on very old version:

    with t as (
               select  date_start + level - 1 dt
                 from  get_dates
                 connect by rowid = prior rowid
                        and level <= date_end - date_start + 1
                        and prior sys_guid() is not null
              )
    select  case grouping(to_char(dt,'mm')) when 1 then to_char(dt,'yyyy') || ' Total' end year,
            to_char(dt,'mm') month,
            sum(case dt - trunc(dt,'iw') when 5 then 1 end) saturday,
            sum(case dt - trunc(dt,'iw') when 6 then 1 end) sunday,
            sum(case dt - trunc(dt,'iw') when 0 then 1 end) monday,
            sum(case dt - trunc(dt,'iw') when 1 then 1 end) tuesday,
            sum(case dt - trunc(dt,'iw') when 2 then 1 end) wednesday,
            sum(case dt - trunc(dt,'iw') when 3 then 1 end) thursday,
            sum(case dt - trunc(dt,'iw') when 4 then 1 end) friday,
            count(*) grand_total
      from  t
      group by grouping sets((to_char(dt,'yyyy')),(to_char(dt,'yyyy'),to_char(dt,'mm')))
      order by to_char(dt,'yyyy'),
               to_char(dt,'mm')
    /
    
    
    YEAR       MONTH   SATURDAY     SUNDAY     MONDAY    TUESDAY  WEDNESDAY   THURSDAY     FRIDAY GRAND_TOTAL
    ---------- ----- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -----------
               05             3          3          3          3          3          3          3          21
               06                        1          1          1          1                                 4
    2014 Total                3          4          4          4          4          3          3          25
               01             1          2          2          2          1          1          1          10
               02             4          4          4          4          4          4          4          28
               03             3          3          3          3          4          3          3          22
    2017 Total                8          9          9          9          9          8          8          60
    
    
    7 rows selected.
    
    
    SQL>
    
    

    SY.

    Shaik_Muhammad
  • Shaik_Muhammad
    Shaik_Muhammad Member Posts: 6 Red Ribbon

    Many Thanks to you Mr. Solomon Yakobson Sir, Mr. EdSteven Sir, report working perfectly.