[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • Last Modified:

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!
0
Nero83
Asked:
Nero83
  • 3
  • 2
3 Solutions
 
vastoCommented:
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
0
 
Nero83Author Commented:
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.
0
 
vastoCommented:
What is your database type ? In SQLServer you can use a function like this one:

CREATE FUNCTION [dbo].[fnGetDatesInPeriod]
(
    @FromDate DATE,
    @ToDate DATE
)
 
RETURNS @DateTable TABLE (DateValue DATETIME)
 
AS
 
BEGIN
    WITH DatesTable(CurrentDate) AS
    (
        SELECT @FromDate AS datetime
        UNION ALL
        SELECT DATEADD(DAY, 1, CurrentDate) FROM DatesTable WHERE CurrentDate < @ToDate
    )
 
    INSERT INTO @DateTable (DateValue)
    SELECT CurrentDate FROM DatesTable OPTION (MAXRECURSION 0)
    RETURN;
 
END

Open in new window


It will return a list of all days between the @FromDate and @ToDate

You can see an example how this function is used here:
http://www.r-tag.com/Pages/Sample_OvertimeCalculation.aspx
0
Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

 
vastoCommented:
You can also generate all  possible dates and save them in a table and filter this table for the records in the last 4 years.

If you are using a function you will need to base your report on a command.
0
 
James0628Commented:
I think vasto has you headed in the right direction, but just to provide some explanation ...

 I see two basic problems with what you were trying to do.

 You basically want to group by the date (each day or month or whatever), but the only date you have in your table is the subscription date.  You could add 365 to that to get the end date, but if you grouped on that you'd just be grouping by the subscription end dates, which isn't what you want either.  For example, the 09/17 group would include only the people with subscriptions ending on 09/17.  You could group by month instead, but then each month would only be the people with subscriptions ending that month, which is still not what you want.

 The second problem is that CR only includes each record in one group.  Even if you were able to get the group to include everyone that was subscribed during that month, it would only be the people that were subscribed during that month, and not included in an earlier group (month) on the report.  Let's say you had the months in descending order, starting with September.  September would include everyone that was subscribed in September.  August would only include the people that were subscribed in August, but not in September (ie. their subscription ended in August), because the people that were still subscribed in September were included already in the September group.  July would only include the people that were subscribed in July, but not in August or September.  And so on.

 I hope that made sense.

 vasto's suggestion to use a table with dates in it handles both of these problems.  You put the dates you want (each day or month or whatever) in that table, and when you link your main table to that, those records are counted separately for each matching date.

 James
0
 
Nero83Author Commented:
Doesn't allow me to do what I wanted, but that's a function of the program not lack of a good response.  Thanks.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now