How to find missing dates in a series

[asimasm]

How can i find missing date vaues in a range from a table
for example if the following values are stored in a table
20-02-2002
21-02-2002
24-02-2002
26-02-2002
and if we find the missing values then the result should be
22-02-2002
23-02-2002
25-02-2002

 

[tlbroadbent]

Check faq183-840. If it doesn't provide the answer you need, please let us know. Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com

 

[Allumo]

Hi.
I've never needed some solution for this situation and I don't know if this is possible with a simgle query. If you don't get it, I suggest two ways:

1. Use a datetime variable within a "while" loop and test each date with a "not exists" row in the table.

2. Create a #Dates table and fill it with all the dates of the needed period. Then execute a "left outer join Table" with a "where Table.Date is null".

Hope this helps you.