Skip to Main Content

SQL Developer

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Need Day wise leave statistic report.

Shaik_MuhammadApr 29 2021

Hi All,
Oracle_Leave_statistic_report_day_wise.jpeg (255.09 KB)
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.

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

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

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 27 2021
Added on Apr 29 2021
2 comments
65 views