Formula to expand a record across a date range


I have a client that wants to create a "date based report" based on a record that includes a date and duration.

For instance, Record 1 - would include a date 7/11/2015 and a duration of "3 days".  

They want to see a report like this:

Group: 7/10/2015
(Detail) *No records*

Group: 7/11/2015
(Detail) Record 1

Group: 7/12/2015
(Detail) Record 1

Group: 7/13/2015
(Detail) Record 1

Group: 7/14/2015
(Detail) *No Records*

Each time "Record 1" is listed it would include other fields from Record 1, I'm just keeping it simple for display purposes.  Also, there could be other records that would be listed within each "date".

Any way this can be done?

Thanks in advance!
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

How far into the future/past?

How long a date range?

Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
Create a Calendar table with one record per date.  Add that table to your report.

Use a record selection formula (or a join if your DBMS supports that type of join) that restricts the joined records to Calendar date >= transaction date and Calendar date <= transaction date transaction date + duration.
Group on Calendar date.
SStrozAuthor Commented:
mlmcc, I was hoping the date range could be added as a date parameter.  It normally would be printed for a week or so at a time but the date range would change.

IdoMillet, I can't add any tables to the database.

The application is for a "Project Record" which has a start date, number of days, number of workers.

They want to create a report that would show each date, the job names for that day, and the number of workers required.  However, if a job spans more than one day they need to also include that job and the number of workers on that day (group) as well.

Any thoughts appreciated!

Here is one method.

It uses several formulas to build the lists and display them


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SStrozAuthor Commented:

Wow - super impressive!

I'm on vacation for a few weeks so I'll have to roll up my sleeves to get it to work with my data set but it looks perfect.

Thanks so much!

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.