Finding Lowest Continuous Date in SQL
Posted on 2004-10-08
I'm fairly new to SQL so please have pitty if this is a silly question, or if I pose it poorly...
I have a table where there are dates associated with members in my system. I'm trying to find the lowest continuous effective date associated with these members. The default value for the termdate, if there isn't one currently is 12/31/2078. Here is some examples from member table:
Name Effdate Termdate
====== ======= ========
John 1/1/2002 12/31/2002
John 1/1/2003 12/31/2003
John 1/1/2004 12/31/2078
Jane 1/1/2001 12/31/2001
Jane 1/1/2002 12/31/2002
Jane 1/1/2004 12/31/2078
For John, I would want to return 1/1/2002. For Jane, however, I would want to return 1/1/2004 since that is the lowest effective date without any breaks in coverage.
I hope this makes some simblance of sense. Any help would be greatly appreciated.