Hi there, I'm new to the forums so I apologize if this is somewhere; I'm also new to Crystal so I'm not even sure what to search for.

Basically, I have a database that stores date ranges, not individual dates, so we have table.StartDate and table.EndDate. Something like bookings in a hotel, with a check in and check out.

What I want to do is lookup, if there were 10 rooms, how many rooms were occupied on X date?

What I would like to do is show, for every date in a range (i.e. this month, or this year), how many rooms were full. (I have more than 10 rooms and I want to sort and group them in different ways, hence the cross-tab)

So I can enter the parameters to show the range I want to show, but I'm having trouble figuring out how to say "for every day in range, was the room full or empty?

Any help would be great. Like I said, I'm very new to Crystal, so even a really obvious answer might help.

Thanks!

if at all possible use a calendar table to join into the other table. This will give you one row per day per occupied room and will make grouping and calculations simple.

That does sound useful, but unfortunately I didn't design the tables and I don't think that's how it is set up :(

Yes, but I'm on a different computer today, so I will have to get back to you. Thanks!

Hey folks, anyone able to provide some assistance?  I'm still stuck on this issue.

I'm trying a new approach: create a date iteration (i.e. Jan 1, Jan 2, Jan 3) and for each day, determine if a specific Stay includes that date.

However, I'm getting errors with my date iteration formula.

I have a parameter, @DateRange, which is what it says.  Note that it is a date variable.

I have a formula, @SelectedDate:

Global DateTimeVar SelectedDate := Minimum({?DateRange})

Yes, I realize this is a DateTime, I'll get back to that.

I have a second formula, @DateIteration:

Global DateTimeVar SelectedDate;
do
    (SelectedDate := DateAdd("d",1,SelectedDate))
while
    (SelectedDate < Maximum({?DateRange}));
SelectedDate

Thing 1: When I call these both a DateVar instead of a DateTimeVar, I get an error with the DateAdd formula, saying that the output needs to be a Date.  No error with DateTime, so I guess that the DateAdd function is converting it to a DateTime?

Thing 2: I'm getting an error that "A loop was evaluated more than the maximum number of times allowed."  Now, for my test, my daterange was only 5 days, so clearly something is going wrong.