Forum Stats

  • 3,874,263 Users
  • 2,266,716 Discussions
  • 7,911,794 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
    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: 19,940 Red Diamond
    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: 19,940 Red 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
    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,778 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: 19,940 Red Diamond
    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.