>Hi,
>
>I need to count the number of days of a date range that fits within another date range. For example:
>
>range1 = 2/10 - 2/15
>range2 = 2/12 - 2/25
>Range1 fits in 4 days on range 2.
>
>range1 = 2/20 - 2/29
>range2 = 2/12 - 2/25
>Range1 fits in 6 days on range 2.
>
>range1 = 2/13 - 2/15
>range2 = 2/12 - 2/25
>Range1 fits in 3 days on range 2.
>
>Furthermore range1 could have an empty end date which means there's no limit to the end date, example:
>range1 = 2/15 -
>range2 = 2/12 - 2/25
>Range1 fits in 11 days on range 2
>
>I'm doing this with IIFs in my query, but I'm thinking maybe someone can think of a more simple way to calculate this.
>
>Thanks!
I see it only as something like
sum(iif (between(myDate, @D1,@D2), 1,0)) as Count1, etc.
If it's not broken, fix it until it is.
My Blog