The above is the sample data output.
1.If the ID and Name are repeated then, it means that there are
more than one entry in the db. But My Query should filter out
the max(enddate) for each person and it should be displayed.
2. If I frame my query to filter the max end date, then I am not
able to get the date calulation for ServiceDays. i.e. I should
check whether the ID is repeated. If so then I should check the
end of the first row and start date of the second. If it is only
lesss than or equal to 180 then I should take the difference
between each start date and end of the both of the rows and add
them. Next if there is any other row with the same ID if yes
then again see the difference between the previous end date and
current start date and if less than one 180, difference between
start date and end date of that row, and add to the previous
total.
So, I should check whether the ID is repeated, untill it is
repeated. I have to find the difference between the first row
end date and start date of second row. In descriptive format it
will be as:-
ID Name StartDate EndDate ServiceDays
5000 XYZZ 05/05/2006 10/10/2006 Null(because only one row)
1001 AAAA 01/01/2006 15/03/2006
1001 AAAA 27/03/2006 30/06/2006 15/03/2006 -27/03/2006 < = 180
then 01/01/2006 - 15/03/2006 = 1223 + (27/03/2006 - 30/06/2006)
= 5678(this value should be shown here against this row as it
holds the max(enddate) for this ID.

>I am writing a new report. I have come up with a small problem,
>which I think you gurus can help me out. The report has data as
>shown below:

I am a little fuzzy on your requirements, but you should be able to
return all the data and filter it to adjust the dates as you
desire. There is code in the code share portion of the site on
suppressing repeating values, if that is a part of your issue.

If the diffrence between 01/01/2007 and 11/01/2006 is less than 180 then DateDiff("d",10/01/2006,11/01/2006) + DateDiff("d",01/01/2007,10/04/2007) then DaysofService = 1234

ID StartDate EndDate DaysofService
1000 01/01/2007 10/04/2007 1234

Only 1000 01/01/2007 10/04/2007 1234 row has to be shown and above row should be omitted as this holds the max(enddate) for this recordset with the same ID. i.e the above row should only be utilised for calculating the daysofService.