Rolling 12-month eligibility by month

[SusanStoHelit]

Hi there,

I'm using Crystal X with SQL Server 2005 and I need to find the last 12 months' eligibility counts broken down by group and month:

Group Mar 06 Apr 06 ....... Feb 07

1 2000 2100 ....... 2500
2 2500 2600 ....... 3000
3 1750 1600 ....... 1800
4 8000 7900 ....... 7500

The problem is that each member can only be in one group but could be in 1 to 12 different months, so I can't use a standard Crystal grouping function.

Eligibility is determined like this:

{ELIGIBILITY.EFF_DT}<= {15th day of month}
and {ELIGIBILITY.TERM_DT}> {15th day of month}
and {ELIGIBILITY.ELIG_IND}="Y"

would an array help?

 

[MrBillSC]

Can you show some sample data of different member scenarios and how you want it reported for each? It's not clear what you want or what the data looks like.
MrBill

 

[SusanStoHelit]

Sure thing. Member 1001 was in group 2 from June 30th 2006 through January 31st 2007. He should be counted in the summaries for Jul-Jan and not the others.

My raw data is like this:
ELIGIBILITY
SBSB_ID GROUP EFF_DT TERM_DT ELIG_IND
1001 2 06/30/2006 01/31/2007 Y
1001 2 02/01/2007 12/31/2199 N
1002 1 01/01/2004 12/31/2199 Y
1003 3 01/01/2005 04/30/2006 Y
1003 3 05/01/2006 12/31/2199 N

So member 1001 will be counted in Jul06-Jan07
1002 will be counted in all of the past 12 months
1003 will be counted in Mar06-Apr06

 

[MrBillSC]

You will need 12 formulas to count for each month. You will also need 2 formulas to calc start and end dates for past 12 months.

//formula StDate - calc the 15th of 12 months ago.
DateAdd("m", -12, minimum(MonthToDate) + 14)

//formula EndDate - Add 11 months to StDate.
EvaluateAfter({@StDate});
DateAdd("m",11,{@StDate})

In record select formula enter:
{ELIGIBILITY.ELIG_IND}="Y" and
{Table.Eff_Dt} <= {@EndDate} and
{Table.Term_Dt} > {@StDate}

//formula Mon01Cnt - is record in 1st month? Place in detail section.
If {Table.Eff_Dt} <= {@StDate} and
{Table.Term_Dt} > {@StDate} then 1 else 0

//formula Mon02Cnt - is record in 2nd month? Place in detail section.
If {Table.Eff_Dt} <= DateAdd("m",1,{@StDate}) and
{Table.Term_Dt} > DateAdd("m",1,{@StDate} then 1 else 0

//formula Mon03Cnt - is record in 3rd month? Place in detail section.
If {Table.Eff_Dt} <= DateAdd("m",2,{@StDate}) and
{Table.Term_Dt} > DateAdd("m",2,{@StDate} then 1 else 0

etc.
Place the 12 formulas in the detail section, then insert > summary and sum the formulas in desired group footers.

MrBill