asked on

grouping active accounts in Crystal Reports

I have a membership-based program where people sign up and their subscription lasts for 365 days.
I am trying to develop a crystal report that shows how many people are signed up at a given time e.g. each day (or instead of each day, I might choose the first day of each month as my date to reference to keep the number of data points fewer.).
There is a date field in the database that signifies the start date of the membership.
I can easily generate a report showing how many people are signed up at a particular date, and a report showing how many people have signed up during a time period, but I'm looking for a way to graph the # of currently enrolled over four years.
I thought it would be easy, but I'm having trouble with the logic or something here. I thought I'd be inserting groups for each day and then counting the subscriptions that fall between that day and 365 days before that day, but I'm having problems.

9/17/12: 1,209 enrolled (includes those enrolling between 9/17/11 and 9/17/12)
9/14/12: 1,235 enrolled (includes those enrolling between 9/14/12 and 9/14/11)
9/13/12: 1,2330 enrolled "" "" 9/13/12 and 9/13/11
.....
4/20/10: 725 enrolled (4/20/10-4/20/09)
...
9/17/08: 112 enrolled (start date of the program was 8/20/08)
8/21/08: 15 enrolled
8/20/08: 0 enrolled
etc.

I need help.

Thanks!

vasto

You should generate a table or a view with the dates for the last for years. I guess it will be enough to have a record per month. Then left join this table with the view presenting the grouped data by month. The result recordset will contain all months for the last 4 years and the number of clients per each month. The same logic could be applied on per day basis , but you will deal with more than 1200 records which might be an overkill.

Once you have the recordset you can create a chart ( I guess your question is not about the chart preparation)

The view to group numbers will be something like this:

SELECT Date, count(*) From <YourTable> GROUP BY Date

The whole SQL will be

SELECT d.Date , t.Customers
FROM Dates d
LEFT JOIN (SELECT Date, count(*) as Customers From <YourTable> GROUP BY Date) t
ON d.Date=t.Date

where Dates is the table with all months/dates for the last 4 years

Nero83

ASKER

How do I generate a table or view - do you mean in another program?

So, one table has a record for each month and the count of the people that enrolled during that month, and the other table has just the dates? how do i eliminate those that have expired after a year from the count? I'm confused...can you give me an example?

Thanks.

SOLUTION

vasto

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

vasto

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial