Friday, June 10, 2011

Getting missing dates in Oracle



WITH DATA AS (
select to_date(to_char(unit_date,'yyyy/mm/dd')) dat from hr_attendance where empid = 'EMP0001'
)
SELECT mi+LEVEL missing FROM(
SELECT MIN(dat) mi,MAX(dat)ma FROM DATA)
CONNECT BY LEVEL <= ma-mi
MINUS
SELECT dat FROM DATA;


  

Share Article : Getting missing dates in Oracle
Share/Save/Bookmark