Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Cross Tab with Date Ranges Post Reply Post New Topic
Author Message
aliryder
Newbie
Newbie


Joined: 20 May 2015
Online Status: Offline
Posts: 17
Quote aliryder Replybullet Topic: Cross Tab with Date Ranges
    Posted: 20 May 2015 at 3:38pm
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!
IP IP Logged
Erik
Groupie
Groupie
Avatar

Joined: 05 Dec 2013
Online Status: Offline
Posts: 50
Quote Erik Replybullet Posted: 21 May 2015 at 5:53am
This sounds a little tricky because I presume that a particular room will have multiple entries for start date and end date specific to each visit.

Is there another identifier in the table for something like visit ID? You'll probably need to tie start date and end date together with this ID to make sure you aren't comparing 2 different visits' start date and end date.

It would also be helpful to know if there is a max # of days for a particular visit to determine the max span of days to look for the end date after the start date.

More questions than answers at this point...
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 21 May 2015 at 5:56am
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.
IP IP Logged
aliryder
Newbie
Newbie


Joined: 20 May 2015
Online Status: Offline
Posts: 17
Quote aliryder Replybullet Posted: 21 May 2015 at 6:00am
Originally posted by Erik

Is there another identifier in the table for something like visit ID? You'll probably need to tie start date and end date together with this ID to make sure you aren't comparing 2 different visits' start date and end date.

Yes, we have StayID.

Originally posted by Erik

It would also be helpful to know if there is a max # of days for a particular visit to determine the max span of days to look for the end date after the start date.More questions than answers at this point...

No, there isn't, but I do have a table for each Room and each Stay is tied to a Room. Is that helpful?
IP IP Logged
aliryder
Newbie
Newbie


Joined: 20 May 2015
Online Status: Offline
Posts: 17
Quote aliryder Replybullet Posted: 21 May 2015 at 6:00am
Originally posted by DBlank

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 :(
IP IP Logged
Erik
Groupie
Groupie
Avatar

Joined: 05 Dec 2013
Online Status: Offline
Posts: 50
Quote Erik Replybullet Posted: 21 May 2015 at 6:29am
It's difficult to conceptualize this without seeing the actual table data. Would it be possible to either list the table names, how they are linked, and what column data you are grabbing from them - or a screenshot of the table structure?
IP IP Logged
aliryder
Newbie
Newbie


Joined: 20 May 2015
Online Status: Offline
Posts: 17
Quote aliryder Replybullet Posted: 21 May 2015 at 8:07am
Originally posted by Erik

It's difficult to conceptualize this without seeing the actual table data. Would it be possible to either list the table names, how they are linked, and what column data you are grabbing from them - or a screenshot of the table structure?

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


Joined: 20 May 2015
Online Status: Offline
Posts: 17
Quote aliryder Replybullet Posted: 22 May 2015 at 4:37am
So the database is massive and I'm only using a part of it for this report.  Here is the piece that's relevant, I think.



http://postimg.org/image/mwo8vbe5t/


Edited by aliryder - 22 May 2015 at 4:43am
IP IP Logged
aliryder
Newbie
Newbie


Joined: 20 May 2015
Online Status: Offline
Posts: 17
Quote aliryder Replybullet Posted: 05 Jun 2015 at 6:32am
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.
IP IP Logged
Erik
Groupie
Groupie
Avatar

Joined: 05 Dec 2013
Online Status: Offline
Posts: 50
Quote Erik Replybullet Posted: 05 Jun 2015 at 12:47pm
Hi aliryder,

Unfortunately I can't currently access the link you provided.

As for your recent post, it sounds like it's causing an endless loop.

One way you might try modifying it is to set a variable to the number of days by subtracting SelectedDate from MaxDate. Then have a counter variable starting out at 0 that you increment each time, and do while counter < #days... and use the counter variable instead of the '1' in your DateAdd maybe?

In looking up the syntax for DateAdd, it returns a DateTime value regardless. It was suggested to use CDate to get it to a strict date value.
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.