missing dates

[Beantree]

Does anyone know a simple way to determine what dates are missing, given a table with dates in it?

For example, I have a table which gets populated with historical data, and I want to find out if any dates are missing.

Thanks

 

[lewisp]

[tt]SELECT *
FROM table
WHERE date_column IS NULL;[/tt]

 

[Beantree]

and how will this tell me which dates are missing?

 

[sem]

What do you mean by "missing dates"? Do you need EVERY DAY to be listed intable? In this case you may try this query:

select * from
(select <start date> - 1 + rownum day
from all_objects
where rownum < <date interval>)
where not exists(select 1 from <your table>
where <date column> = day)

It's asumed that all_objects contains not less than <date interval> records.

Regards, Dima

 

[Beantree]

The table has roughly 70,000 records per day that we receive a file. There are days we didn't receive a file for both valid reasons (weekends/holidays) and invalid reasons (FTP failed, no file provided, etc)

I am trying to find the days missing so that I know what days to attempt to retrieve.

 

[sem]

Does your table contains a date column? If yes
it's even more easier if you have some calendar table:

select <calendar date> from <calendar table> c
where not exists(select 1 from <your table>
where <date column> = c.<calendar date>)

Regards, Dima

 

[Beantree]

GOT IT,

Thanks SEM

select * from
(select to_date('12292000', 'MMDDYYYY') - 1 + rownum day
from all_objects
where rownum < 844)
where not exists(select 1 from t_positions_arc
where record_date = day)
AND TO_CHAR(DAY,'DAY') NOT IN ('SATURDAY ','SUNDAY ')