AncientFrib
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hello - sorry for the delay. Running Crystal 2008.
ASKER
A time period wouldn't typically span more than a year by the way.
mlmcc