Oracle Database Discussions

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.

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”