This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,899 Users
  • 2,269,649 Discussions
  • 7,916,821 Comments

Discussions

Need Day wise leave statistic report.

Shaik_Muhammad
Shaik_Muhammad Member Posts: 6 Red Ribbon

Hi All,


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

Answers

  • User_H3J7U
    User_H3J7U Member Posts: 1,062 Gold Trophy

    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
    EdStevens Member Posts: 28,778 Gold Crown

    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.

This discussion has been closed.