Forum Stats

  • 3,872,126 Users
  • 2,266,392 Discussions
  • 7,911,052 Comments

Discussions

Categories

Need Day wise leave statistic report

Shaik_Muhammad
Shaik_Muhammad Member Posts: 6 Red Ribbon
in General Database Discussions
Oracle_Leave_statistic_report_day_wise.jpeg

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”

Tagged:
· Share on TwitterShare on Facebook

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

    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,911 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
    · Share on TwitterShare on Facebook

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,911 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.

    · Share on TwitterShare on Facebook
  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown

    You posted the exact same problem yesterday .... https://community.oracle.com/tech/developers/discussion/4483855/need-day-wise-leave-statistic-report#latest

    · Share on TwitterShare on Facebook
  • 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..

    · Share on TwitterShare on Facebook
  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown

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

    Ah, so I did. Mea culpa.

    · Share on TwitterShare on Facebook
  • Shaik_Muhammad
    Shaik_Muhammad Member Posts: 6 Red Ribbon
    https://community.oracle.com/tech/developers/discussion/comment/16793986#Comment_16793986

    Thanks for the reply.

    We are using 11g database.

    Attached is the query execution output.

    query_execution.jpg


    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,911 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
    · Share on TwitterShare on Facebook
  • Shaik_Muhammad
    Shaik_Muhammad Member Posts: 6 Red Ribbon

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

    · Share on TwitterShare on Facebook