Number of records per days after start

[hansdebadde]

I am using CR 2008, my data is in SQL. I have a table that has peoples records with a START_DATE and END_DATE. To compare how many people are in the period i want to count the number of people per days after start date. For example, if one person has a start date of jan 1, and an end date of dec 31 and another guy has a start day of june 1 and an end date of june 30 then there would be a count of 2 people for 1-30, 1 person for 31 to 365 and then none after that. Of course my dataset has a lot more people. I hope this makes sense. My hope is then to graph the count of people per this number of days after start. I hope this makes sense.
Thanks
PB

 

[hilfy]

You're probably going to have to write a command to do this - I don't see how to do it directly in Crystal. Also, you're probably going to need some sort of Calendar table that contains every date in it. Assuming table names of Calendar and Employee, your SQL Command would then look something like this:

Select
  cal.calendardate,
  count(emp.ID) as totalcount
from Calendar as cal
  inner join Employee as emp
  on cal.calendardate between emp.start_date and emp.end_date)
group by cal.calendardate

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[lbass]

I think hilfy's approach is probably a better one, but I also think you could insert running totals, one for each date range (since you need to count the same row multiple times) where you do a distinctcount of the person ID, evaluate using a formula:

datediff("d",{table.startdate},{table.enddate}) in 31 to 365

Reset never.

The chart (in the report footer) would have to have multiple running totals entered as summaries. Alternatively, you could do this with conditional formulas, but again, you'd have to add separate summaries per range.

-LB

 

[hansdebadde]

Thanks, both of you, i will be back Monday. I can't think of a calender table, so i will likely try running totals.

 

[hansdebadde]

LB, that worked great... with one issue. I am doing more than just the two periods. I actually did one for each month for 3 years. That was a lot of running totals. I then chart per year of start date to compare each years performance. The only prob though is that i am getting a cummulative total for each month. For example, month one after start date for 2010 is a sum of 2005-2010 mnth 1, mnth 1 2009 is 2005-2009 mnth 1, mnth 1 2008 is 2005-2008 mnth 1, etc. Do i need to do conditional running totals for each year or is there something a lot less time consuming that i am not considering? Thnx

 

[hansdebadde]

BTW, of course I am not using the stacked line graph

 

[hansdebadde]

I figured it out, and did it a completely different way.
i made an excel sheet with three columns: month, start, end
for example:
1 0 60
2 31 61

I then added this as a datasource to the mainreport and grouped by Sheet1.month

I did print time counters in a main report
//accum in
whileprintingrecords;
shared numbervar d2007;

if {DATA.START_DATE} in CDateTime (2007, 01, 01, 00, 00, 00) to CDateTime (2007, 12, 31, 23, 59, 59)
and
datediff("d", {DATA_START_DATE},{DATA.CLOSED_DATE}) in {Sheet1_.Start} to {Sheet1_.End}
then d2007:=d2007+1

I also did counter, reset and total (totperperiod) formulas in header, header and footer respectively.

I did formulas for each or 2005 to 2010

I then did OnChangeOf formula:
whileprintingrecords;
stringvar strOnChangeOf;
strOnChangeOf:=strOnChangeOf + totext({Sheet1_.Start}) + "^";

I also did ShowValue formulas for 2005 to 2010:
stringvar strShowValue2007;
strShowValue2007:=strShowValue2007 + totext({@2007 totperperiod})+"^";

I put the OnChangeOf and ShowValue formulas in the last footer.

I inserted a subreport in the report footer and passed the OnChangeOf and ShowValue formulas into it.

I did a formula in the subreport for each year
//2007
tonumber(split({?Pm-@Show value2007}, "^")[RecordNumber])

I then was finally able to chart lines of totals per month for each year