Need Day wise leave statistic report.

Shaik_Muhammad

Hi All,

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”

User_H3J7U

Connect by with the condition on only level is applicable to a single-row source. Otherwise, the source rows are multiplied by each level.

Use join on correlated subquery with dual connect by (cross apply).

EdStevens

A couple of hints:

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

2) When you post code, it is much (MUCH) preferred that you format it so that it is more easily read:

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;
 
select date_start, date_end, date_end - date_start +1 days FROM GET_DATES;

 Notice also that your TO_DATE is lacking a format mask. That is just asking for trouble.