Oracle Query To Get Total Count of Pending Counts Department Wise And Drill Down Data In Oracle

In this blog, you will see the Oracle query to get the total count of pending counts department wise and drill down data In Oracle.

Oracle Query to calculate total count of projects:

SELECT DEPOSITORY_NAME,COUNT(*) FROM AX_FRTNT_ENTRY_MASTER_T WHERE ENTRY_ACTIVE_FLG = 'Y' AND ENTRY_CLOSED_STATUS = 'N' GROUP BY DEPOSITORY_NAME ORDER BY 2 DESC

 

Oracle Query to calculate pending with count:

ELECT NVL(PENDING_WITH,'Not Assigned'),COUNT(*) FROM AX_FRTNT_ENTRY_MASTER_T WHERE ENTRY_ACTIVE_FLG = 'Y' AND ENTRY_CLOSED_STATUS = 'N' GROUP BY PENDING_WITH ORDER BY 2 DESC

 

Oracle Query to calculate days of all projects:

SELECT CS,COUNT(*) FROM ( SELECT START_OF_ACTIVITY,FLOOR(SYSDATE-START_OF_ACTIVITY),CASE WHEN FLOOR(SYSDATE-START_OF_ACTIVITY) > 365 THEN 'More than 1 year' WHEN FLOOR(SYSDATE-START_OF_ACTIVITY) < 366 AND FLOOR(SYSDATE-START_OF_ACTIVITY) > 200 THEN 'Between 365-201' WHEN FLOOR(SYSDATE-START_OF_ACTIVITY) < 201 AND FLOOR(SYSDATE-START_OF_ACTIVITY) > 100 THEN 'Between 200-101' WHEN FLOOR(SYSDATE-START_OF_ACTIVITY) < 101 AND FLOOR(SYSDATE-START_OF_ACTIVITY) > 50 THEN 'Between 100-51' WHEN FLOOR(SYSDATE-START_OF_ACTIVITY) < 51 AND FLOOR(SYSDATE-START_OF_ACTIVITY) > 25 THEN 'Between 50-26' ELSE 'Less than 25' END CS FROM AX_FRTNT_ENTRY_MASTER_T WHERE ENTRY_ACTIVE_FLG = 'Y' AND ENTRY_CLOSED_STATUS = 'N' ) GROUP BY CS ORDER BY 2 DESC

 

Oracle Query to drill down to calculate number of days, and total pending:

SELECT PENDING_WITH, SUM(ROUND(Days_to_next_start)) AS Days_to_next_start FROM ( select STATUS_AS_ON_DATE,PENDING_WITH, (lead(STATUS_AS_ON_DATE,1) over (order by STATUS_AS_ON_DATE)-STATUS_AS_ON_DATE)*24/24 as Days_to_next_start from ax_frtnt_entry_status_master_t where entry_id=? GROUP BY STATUS_AS_ON_DATE,PENDING_WITH ORDER BY 1 ASC) GROUP BY PENDING_WITH