multiple date gaps in records within a group 1

[VE]

I have googled and experimented in both CR alone and with SQL commands and I'm going crazy...

here is what I'm trying to do (CR2008 and SQL2008):

Look at record dates by person, and if there is less than 7 months between the dates then I accrue a number of hours based on a formula, if the gap goes over 7 months then the hours have to be reset to 0.

So far I have been able to get to the current and last if the gap is less than 7 months in both CR and SQL several ways, but I know that some individuals have multiple gaps that are less than 7 months.

Thank you so much in advance.

VE

 

[PeterDimitrov]

This is a "gap and island" case. Prepare a set of records containing the start and end date for each period and summarize the data (if necessary).
You can google "gap and island sql" for samples.
Here are some:

http://technet.microsoft.com/en-us/library/aa175780(v=sql.80).aspx

https://www.simple-talk.com/sql/t-sql-programming/the-sql-of-gaps-and-islands-in-sequences/

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports and SSRS.

 

[VE]

Wow! thank you! I had no idea this had a name, so much easier to find when you know the correct terminology.

VE