Group by day with date range

Posted on 2011-05-04
Last Modified: 2012-05-11

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.
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.

    LVL 100

    Expert Comment

    What version of Crystal are you using?

    LVL 5

    Author Comment

    Hello - sorry for the delay.  Running Crystal 2008.
    LVL 5

    Author Comment

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

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    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.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now