Dynamic Date Range?

[aellistechsupport]

I'm trying to figure out how to set a limit, a date range limit for a year span.

A season is Jul - Jun for most members.
Another words, in this current season it would be 7/1/13 - 6/30/2014 for Regular Members



For other members their season is 10/1/13 - 9/30/2014.

How do I set the season end date (EndDate) of their membership so that the dates do not have to be manually changed.
So that it dynamically changes when the time comes?

So right now to do something like
YEAR(attribute.PersonMembership.EndDate) = YEAR({ fn CURDATE() })

Full statement here:

SELECT lookup.MemberTypes.Id AS MemberTypeID, lookup.MemberTypes.MemberGroup, lookup.MemberTypes.Description AS MemberType, entity.Person.Status,                   entity.Person.MembershipNumber, attribute.PersonMembership.StartDate, attribute.PersonMembership.EndDate, attribute.PersonMembership.PaymentDate, 
            YEAR(attribute.PersonMembership.EndDate) AS EndYear, 
                  attribute.PersonMembership.PersonId
FROM     entity.Person INNER JOIN
                  attribute.PersonMembership ON entity.Person.Id = attribute.PersonMembership.PersonId RIGHT OUTER JOIN
                  lookup.MemberTypes ON attribute.PersonMembership.MembershipTypeId = lookup.MemberTypes.Id
WHERE  (lookup.MemberTypes.Id BETWEEN 24 AND 31) AND (YEAR(attribute.PersonMembership.EndDate) = YEAR({ fn CURDATE() }) OR
                  YEAR(attribute.PersonMembership.EndDate) IS NULL)

I do get all the records with their membership ending this year.
HOWEVER when June comes this year and when September comes this year the Year limit to this year won't work. Because that's the start of the next season.

I hope I made sense.

Right now I can't think of a way to set it so there is no user entry to change it when the new season comes in the current year.

I'm thinking something like this:
MONTH(attribute.PersonMembership.EndDate) as EndMonth = 6
YEAR(attribute.PersonMembership.EndDate) as EndYear = YEAR(GETDATE())

but at a loss on when July comes since starting July (and for the other members September) the Year(GETDATE()) doesn't work because it's only looking at this year. It needs to look at next year (next season).

[June7]

I do not understand the issue.

If a membership is always 12 months from start date, calculate end date by adding 12 months. If year for end date is same as the current calendar year (or otherwise specified year), then retrieve the record.

[aellistechsupport]

This is another option
Between DateAdd("yyyy",-1,DateSerial(Year(Date()),7,1)) And DateSerial(Year(Date()),6,1)
Between DateAdd("yyyy",-1,DateSerial(Year(Date()),10,1)) And DateSerial(Year(Date()),9,1)

I'm thinking doing a date range on the Year.

MONTH(attribute.PersonMembership.EndDate) EndMonth = 6 OR IS NULL or 9 OR IS NULL for FOFS members
YEAR(attribute.PersonMembership.EndDate) EndYear BETWEEN YEAR(GETDATE()) AND YEAR(GETDATE()) OR IS NULL

[June7]

You want to automatically change the member's membership record to reflect renewal?

Do you have a table for history of enrollments? Shouldn't you input a new enrollment record?

[aellistechsupport]

Goal is to see the records from the membership history of only those records that end in the current season.

Which encompasses:
Current season = July 2013 - June 2014 for the Regular members <-- when July 1, 2014 comes around the "current" season would then be July 2014 - June 2015.
Current season = Oct 2013 - Sep 2014 for the FOFS members <-- when Oct 1, 2014 comes around the "current" season would then be Oct 2014 - Sep 2015.

So looking for a way to set the criteria to show the above.

[June7]

Bizarre! Why two annual enrollment periods? An end date is always either 6/30/2015 or 9/30/2015, nothing like 2/13/2015?

You mean the current season from the perspective of each record?

This is twisting my brain in knots but I will offer:

WHERE EndDate = DateSerial(Year(Date()) + IIf((Month(EndDate)=6 AND Month(Date()) < 7) OR (Month(EndDate)=9 AND Month(Date())<10), 0, 1), IIf(Month(EndDate)=6, 6, 9), 30)

Date() could be substituted with a reference to a control on form for input.

[aellistechsupport]

lol! right!?
They are trying to get ALL their memberships to end on June.
BUT in the meanwhile until that happens, who knows how long that'll take?

Hopefully they have implemented it so just a year is left for the switch over to take place when all of the Sep. expires and they are on the June cycle too.

I'll try your suggestion and see what it produces. Unfortunately it won't be until Monday.

Thank you!

[aellistechsupport]

So I went with this:

1st row criteria:
Year([PaymentDate]) = Year(Date())
Month([PaymentDate]) = 6 or 9

2nd row criteria:
Year([PaymentDate]) = Year(Date())+1
Month([PaymentDate]) = 6 or 9