Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Need Day wise leave statistic report

Shaik_MuhammadApr 30 2021

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”
This post has been answered by Shaik_Muhammad on May 2 2021
Jump to Answer

Comments

Aketi Jyuuzou
I used sense of Tabibitosan B-)
870646

In this case,I imagine four traveler ;-)
create table t(prod,order_date,order_nbr) as
select 'ABC', to_date('6/12/2009','fmmm/dd/yyyy'), 1 from dual union all
select 'ABC', to_date('6/13/2009','fmmm/dd/yyyy'), 1 from dual union all
select 'ABC', to_date('6/14/2009','fmmm/dd/yyyy'), 1 from dual union all
select 'ABC', to_date('6/15/2009','fmmm/dd/yyyy'), 0 from dual union all
select 'ABC', to_date('6/16/2009','fmmm/dd/yyyy'), 0 from dual union all
select 'ABC', to_date('6/17/2009','fmmm/dd/yyyy'), 1 from dual union all
select 'ABC', to_date('6/18/2009','fmmm/dd/yyyy'), 1 from dual union all
select 'ABC', to_date('6/19/2009','fmmm/dd/yyyy'), 1 from dual union all
select 'ABC', to_date('6/20/2009','fmmm/dd/yyyy'), 1 from dual union all
select 'ABC', to_date('6/21/2009','fmmm/dd/yyyy'), 1 from dual union all
select 'ABC', to_date('6/22/2009','fmmm/dd/yyyy'), 1 from dual union all
select 'DEF', to_date('6/18/2009','fmmm/dd/yyyy'), 1 from dual union all
select 'DEF', to_date('6/19/2009','fmmm/dd/yyyy'), 1 from dual union all
select 'DEF', to_date('6/20/2009','fmmm/dd/yyyy'), 0 from dual union all
select 'DEF', to_date('6/21/2009','fmmm/dd/yyyy'), 0 from dual union all
select 'DEF', to_date('6/22/2009','fmmm/dd/yyyy'), 0 from dual;

select prod,min(order_date),count(*)
from (select prod,order_date,order_nbr,
       Row_Number() over(partition by prod           order by order_date)
      -Row_Number() over(partition by prod,order_nbr order by order_date)
      as distance
        from T)
 where order_nbr = 1
group by prod,distance
order by min(order_date);

PRO  MIN(ORDE   COUNT(*)
---  --------  ---------
ABC  09-06-12          3
ABC  09-06-17          6
DEF  09-06-18          2
1 person found this helpful
Frank Kulash
Hi,

Thanks for posting the CREATE TABLE and INSERT statements: that really helps!

Aketi seems to be assuming that order_nbr is always 0 or 1, and that the order_dates in your table are consecutive days (which happens to be true in your sample data).
If that's not the case, you can modify Aketi's solution like this: 
WITH	got_grp	    AS
(
	SELECT	product_name
	,	order_date
	,	order_nbr
	,	ROW_NUMBER () OVER ( PARTITION BY  product_name
			      	     ORDER BY  	   order_date
				   ) + ( TRUNC (SYSDATE) 
				       - TRUNC (order_date)
				       )      		AS d_grp
	FROM	tt1
	WHERE	order_nbr	> 0
)
SELECT	  product_name
,	  MIN (order_date)	AS start_date
,	  COUNT (*)		AS consecutive_days
FROM	  got_grp
GROUP BY  product_name
,	  d_grp
ORDER BY  product_name
,     	  start_date
;
As written, this assumes that the combination (product_name, TRUNC (start_date)) is unique. If that's no the case, it can be adapted.
1 person found this helpful
Aketi Jyuuzou
Oh I did not realize that I can use "where clause" in first select :-)
Likewise my previous solution,I imagine four traveler 
select prod,min(order_date),count(*)
from (select prod,order_date,order_nbr,
      order_date-Row_Number() over(partition by prod order by order_date)
      as distance
        from T
       where order_nbr > 0)
group by prod,distance
order by min(order_date);

PRO  MIN(ORDE   COUNT(*)
---  --------  ---------
ABC  09-06-12          3
ABC  09-06-17          6
DEF  09-06-18          2
1 person found this helpful
85972
Thank you. I worked :)
1 - 4

Post Details

Added on Apr 30 2021
#general-database-discussions, #pl-sql, #reporting, #reports, #sql
7 comments
103 views