I want to the user to be able to select a group /multiple groups and year/multiple years. My table contains the groups but does not contain a year value - this needs to be calculated based on the data in the table (EffDate and EffToDate).
I used a List Box and populated it with the values from the product column - multiple selections can be made. I have not problem setting this up.
Next, I want a List Box or Input Box with a list of years. Multiple selection is required. Having trouble with this.
I have the following Table: Member Table containg the Group the Member belongs to, the Member Number, the Beneficiary Number, EffDate (date at which Member joined the Group), EffToDate (date at which Member left the group).
Not the following: EffToDate Column: The date field can contain a date, eg Future Dated 9999/01/01, it can contain 'n NULL value or the Field can contain nothing.
Also note: The could be more than one group.
Say for instance the following selection is made: Product = Group 1 and Year = 2012:
In SQL I would do the following check: I require all Members active in 2012 for Group1:
declare @StartDate datetime = '2012-01-01'
declare @EndDate datetime = '2012-12-31'
declare @Product char(50) = 'Group1'
from Table o
o.Product = @Product and
o.EffToDate< @EndDate and
o.EffToDate > @StartDate or
o.EffToDate is null or
ltrim(rtrim(o.EffToDate)) = '' or
o.EffToDate > getdate()
Once I have the info, I would do a Distinct Count of MemNum + BenfcNum and Group by Product.
The End Result should be a Distinct Count per Product based on the Product and Year selection the User has made.
If it was a single Date field, you could simply calculate the Year field by using Year(EffDate) statement in the script.
Since it seems you are using two dates (a From date and a To Date), you could try calculating the Year field like this:
This would give you a Year field containing year values from both Date fields. Or you could even try calculating all the years between the from and to dates (say, the min(year) from EffDate and Max(Year) from (EffToDate) and then evaluate year.