Complex Date-grouping problem

[vestigo]

Hey guys, thanks for your help.

Here's what I'm trying to do...

I have people who are booking various hotel rooms for a period of time. I need to create a report that shows how many rooms are booked for each day of the year.

As an example:

- Mr. X has booked a room in Hotel 1 from March 3rd to March 17th.
- Mr. Y has booked a room in Hotel 2 from March 14th to March 22nd.
- Mr. W has booked a room in Hotel 1 from March 7th to March 30th.
- Mr. Z has booked a room in Hotel 2 from March 1st to March 31st.

I need a report that is grouped by each month of the year, then sub grouped by each day within the month, then sub grouped by hotel... which finally shows how many rooms are booked in each hotel, and how many rooms are still available to be booked.

So...

March 2009
Monday, 1st
Hotel 1: 0 Booked 20 Available
Hotel 2: 1 Booked 19 Available
Tuesday, 2nd
Hotel 1: 0 Booked 20 Available
Hotel 2: 1 Booked 19 Available
Wednesday, 3rd
Hotel 1: 1 Booked 19 Available
Hotel 2: 1 Booked 19 Available

Etc, etc.

The grouping, etc, is fine. My problems are

- I need a day and a hotel to show up even if there are bookings for it
- How do I show a room is booked for each day, when all I have is a checkin date and a checkout date?

This report is for a client, not myself, so there's really not much room to re-structure it (grouping-wise).

Thanks again!

 

[IanWaterman]

You need an extra table

This will be a list of hotels then join your current data with a left outer join from the Hotels table. YOu can then group on hotel from this new table and all will show with and without bookings.

Getting all days to list is mch more complex, I have only done this before by building a stored procedure to expand dataset to get a record for each days booking.
Might be able to do same with a command.

Ian

 

[Madawc]

What's your data structure? Do you have a table for hotels, or just booking details?

If you can get new tables set up, have one for all hotels and another for all days. Then group by day and by hotel within day. Note that Crystal can read from several sources, including an Excel spreadsheet.

If this isn't possible, then you'll need to use a mock-crosstab. Normal crosstabs are found under Insert in most versions of Crystal (and it's best to say what you use, 8, 8.5, 9, 10, 11 or whatever). But a crosstab won't meet all your needs, probably.

A 'Mock Crosstab' is something that looks like a Crosstab, but in fact you define each column yourself, normally as a running total. This would need to go in the report footer, because running totals count as the reports 'run' and they will not be complete until then. Crystal should have included an example along with the Crosstabs.

You can save a little time by doing a paste to a dummy report, changing the name and then pasting back.

Each running total will count the record if it was within the criteria - in your case, days and hotels.


Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10