finding gaps in datasets

[subwayman]

I have a table primary keyed on two fields: date and storeid. I want to find gaps in the records. All stores should have a record for each day. Records are entered manually and correspond to each day's trading. What I would like to do is present the first 'space' up to and including the current day when a user chooses to enter records for a store. My SQL is novice level and the only way I can see to find the first gap is trawl through the dataset on storeid. However, it seems such a simple requirement there is probably a smart way to do it. Anyone, help? BTW the first thing I do is query the last record by date and if it was yesterday then no problem - present a record for today. But a gap in the records is beyond me.

[Sxooter]

The way I do it is to create a temporary table with all the date ranges in it, left join from it to the data set, and select everything where the corresponding fields are null:

select singledate from datetable dt left join salestable st on (dt.date=st.date) where dt.date between '2005-05-01' and '2005-05-31' and st.somefield IS NULL

[subwayman]

Thanks. I thought there might be a real easy way but perhaos not. Your suggestion is better then my current solution. Thanks for your help.

[Sxooter]

You're welcome. The old "left join / where null trick is a very useful one to have in your bag.