Date ranges in date list 1

[BrianOg]

Given a range of dates held in a field in a table or generated by a query, e.g.:
10/1/04,11/1/04,12/1/04, 23/1/04, 3/2/04,4,2,04, 8,2,04
How can I write a query to generate:

fldStartGap fldEndGap
13/1/04 22/1/04
24/1/04 2/2/04
5/2/04 7/2/04

Would appreciate anyone's help

Brian

 

[Golom]

Try something like

Select (A.DateField + 1)      As fldStartGap,

       (MIN(B.DateField) - 1) As fldEndGap

From   tbl As A INNER JOIN tbl As B
       ON B.DateField > A.DateField

GROUP BY A.DateField + 1

HAVING A.DateField + 1 <= MIN(B.DateField) - 1