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;
Subscribe to:
Post Comments (Atom)
0 comments:
Post a Comment