Hey guys,
I try to select a startdate out of a list of Dates. Let me explain, how the Table isbuild up:
Column 1: ID
Column 2: Startdate
Column3: Enddate
Column4: ID2
Column5: ID3 (only 1 or 0)
Now, I want to compare the Startdate, where ID3 is 1 with the enddate where ID3 is 0. If the enddate with ID3 = 0 and enddate <= startdate (ID3 = 1) - 6 Weeks, then DATE = '2012-11-01' , else startdate (ID3 = 1)

Examaple1 :
Startdate by ID3=0: 2011-06-01
Enddate by ID3=0: 2012-10-31
Startdate by ID3=1: 2012-11-01
Result: 2012-11-01
Example2:
Startdate by ID3=0: 2012-05-25
Enddate by ID3=0: 2012-06-30
Startdate by ID3=1 2012-11-01
Result: 2012-11-01

There is an other option: There is a possibility, that ID3=1 must comapare with the startdate of other ID3=1 Startdates.
Example3:
Startdate by ID3=1: 2012-12-05
Enddate by ID3=1: 2012-12-20
Startdate by ID3=1: 2013-01-01
Result: 2012-12-05
If there is no ID3=0 or the startdate is older then 6 weeks, then compare ID3=1 with ID3=1 and compare the startdate with the last enddate of ID3=1, if the enddate of the last ID3=1 is bigger then startdate by ID3=1 - 3 Month then take the startdate of the last ID3=1 Date

I think it is hard to understand, but maybe, someone has an idea.

thanks

erik