Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 find missing dates in a table

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-12-12 : 07:27:59
jay writes "Hi All

I have several tables (200) that have daily data in them. each row has a date time field. i expect data for every day of the year.

I need to find a way to identify missing dates in the table, ie
if the table has the following rows:

Date data1 data2
1/1/2005 5 7
2/1/2005 6 8
4/1/2005 5 11

i need to show that there is no row for 3/1/2005. I am stumped

thanks in aticipation of your help.

cheers jay"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-12 : 07:42:25
You need to make use of other table that has series of Dates and join to that to find missing Dates

Select dateCol from DateTable D where not exists(select * from yourTable where datecol=D.datecol)

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -