>>Hey all
>>
>>Without resorting to some looping code, I'm trying to meet a business rule. If a person works different shifts in a single calendar week, they pay dues once for that calendar week.
>>
>>

>>Start        End
>>2007-12-02 2007-12-02 <- 2007 week 49 to 2007 week 49
>>2007-12-04 2008-01-02 <- 2007 week 49 to 2008 week 1

>>
>>So I want to include only the second record because the 1st week is already accounted for in the second record.
>>
>>Further, is there no way to express weeks as a number that spans several years to simplify subtraction?
>>
>>Thanks
>
>Does it help to reduce to one record by doing a MIN(start) and MAX(end)?

I was thinking along the lines of min(week(start)) but week() doesn't cross years - which will be needed to calculate elapsed weeks.