Corelated subqueries in oracle

 Below is a complex implementation of corelated subquery in oracle.

Also this query demonstrates the implementation of PIVOT in oracle.

In the query below we are demonstrating the calculation of Number of employees joined TODAY, IN THE MONTH AND IN THE YEAR(YTD).

QUERY: 

SELECT TAB1.ROWNAME, TODAY_TOTAL, MONTH_TOTAL, YEAR_TOTAL FROM
(
  SELECT COUNT(*) AS TODAY_TOTAL,'GETDATA' AS ROWNAME FROM EMPLOYEE EMP
  WHERE
  EMP.DATEJOINED > TO_DATE(TO_CHAR(SYSDATE-1,'DD-MON-YYYY'),'DD-MON-YYYY')
  AND EMP.DATEJOINED < TO_DATE(TO_CHAR(SYSDATE,'DD-MON-YYYY'),'DD-MON-YYYY')

) TAB1
 JOIN
(
SELECT COUNT(*) AS MONTH_TOTAL,'GETDATA' AS ROWNAME FROM EMPLOYEE EMP
WHERE
EMP.DATEJOINED > > to_date(to_char(first_ofmonth(sysdate),'DD-Mon-YYYY'),'DD-Mon-YYYY')
AND EMP.DATEJOINED > < TO_DATE(TO_CHAR(SYSDATE,'DD-MON-YYYY'),'DD-MON-YYYY')

) TAB2 ON TAB1.ROWNAME = TAB2.ROWNAME
JOIN
(
SELECT COUNT(*) AS YEAR_TOTAL,'GETDATA' AS ROWNAME FROM EMPLOYEE EMP
WHERE
EMP.DATEJOINED > > TO_DATE('01-APR-'||PRVYEAR||':23:59:59','DD-MON-YYYY:HH24:MI:SS')
AND EMP.DATEJOINED > < TO_DATE(TO_CHAR(SYSDATE,'DD-MON-YYYY'),'DD-MON-YYYY')

) TAB3 ON TAB1.ROWNAME = TAB3.ROWNAME;

The function for calculating the first day of month in oracle can be found at below location:

http://shareviewsnative.blogspot.in/2012/07/fisrt-day-of-month-in-oracle.html