• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 387
  • Last Modified:

Group by day with date range

Hello,

I have a bunch of records that have an active beginning date and an active ending date.  This date range represents the time frame in which this record was "active".

I need to create monthly statistics based on these records.  The user will provide a date range and I need to show how many records were active on EACH day within the provided date range.

I have a select statement worked out to only pull records that were active at some point within the provided date range.  How can I group by each day within the provided date range to show the number of active records for each day within the provided date range.

For example, if I had the following 2 records:

name: test 1
active beginning date: 1/1/11
active end date: 1/15/11

name: test 2
active beginning date: 1/13/11
active end date: 1/31/11

If the user provided date range was Jan 1 through Jan 31, I should show that (for EACH DAY) 1/1-1/12 had only 1, 1/13-1/15 had 2, and 1/16 to 1/31 had 1.
0
AncientFrib
Asked:
AncientFrib
  • 2
  • 2
1 Solution
 
mlmccCommented:
You can't do it by grouping since a record can only appear in 1 group.

2 ideas come to mind
1.  Use a subreport for each day in the range.  You will probably need a table that has all date in the potential range.

2.  Use formulas and arrays to count the totals.

How long a period might they ask fo?
AN array can only have 1000 element so that would allow about 3 years in dates.

mlmcc
0
 
mlmccCommented:
What version of Crystal are you using?

mlmcc
0
 
AncientFribAuthor Commented:
Hello - sorry for the delay.  Running Crystal 2008.
0
 
AncientFribAuthor Commented:
A time period wouldn't typically span more than a year by the way.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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