>>>I have an accounting table with transactions entered daily for funds. I need to select from the transaction table to make sure all dates have been loaded. I need to check that every day from 9/1/2006 thru today are represented in the file for all funds, and print a list of the missing days, i.e.
>>>
>>>
>>>fund trans_date
>>>ABC 10/14/06
>>>ABC 3/14/07
>>>DEF 1/4/07
>>>
>>>
>>>
>>>I have been going round and round with this. I'm not sure the best way to implement it. How would you do it?
>>
>>
>>Check interesting link
http://book.itzero.com/read/others/0602/OReilly.SQL.Cookbook.Dec.2005_html/0596009763/sqlckbk-CHP-9-SECT-10.html for ideas.
>
>thanks for the link, i've marked it for future use, but I didn't see anything about selecting dates not in a range of dates.
The idea is to create a table with all dates in the selected range. Or just have a Calendar table with all dates. Then it's easy to find missing dates not in the range.
You may check
http://tek-tips.com/viewthread.cfm?qid=1364597 (read the whole thread and in the bottom there is Dates table creation).
If it's not broken, fix it until it is.
My Blog