Forum Stats

  • 3,758,102 Users
  • 2,251,328 Discussions
  • 7,870,030 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: 467 Bronze 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,458 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.