Solved

# Group by day with date range

Posted on 2011-05-04
385 Views
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
Question by:AncientFrib

LVL 100

Accepted Solution

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

LVL 100

Expert Comment

What version of Crystal are you using?

mlmcc
0

LVL 5

Author Comment

Hello - sorry for the delay.  Running Crystal 2008.
0

LVL 5

Author Comment

A time period wouldn't typically span more than a year by the way.
0

## Featured Post

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
how to add IIS SMTP to handle application/Scanner relays into office 365.