Link to home
Start Free TrialLog in
Avatar of AncientFrib
AncientFribFlag for United States of America

asked on

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.
ASKER CERTIFIED SOLUTION
Avatar of Mike McCracken
Mike McCracken

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mike McCracken
Mike McCracken

What version of Crystal are you using?

mlmcc
Avatar of AncientFrib

ASKER

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