1. 

   haigda, Member

   Morning

   I am looking for some help

   I have a table with a date of arrival, name of a hotel, length of stay and a price. I am trying to display it grouped by the length of stay, listing the dates as the rows, the hotel names as the columns and the price as the data in the grid. The further complication is that I want to show the dates that don't exist in the table as well as those that do - ie to show a whole month of data when I only have information in the base table for Tuesdays and Thursdays.

   I'm playing about with crosstabs as this seems to be the way to go but if you have any advice I would appreciate it!

   I don't seem to be able to paste an image of the Excel sheet I am trying to emulate but hopefully you can see it from this link Sample Hotel Price Panel - I'd also be interested in knowing if there is a way to do the 'slanting' text as well.

   Cheers,
   David
2. 

   haigda, Member

   Apologies, the link seems malformed - here it is as text .... http://www.glentonholidays.co.uk/images/pdf/Scan.pdf

   Cheers,
   David
3. 

   myDBR Team, Key Master

   To show dates that do not exist in your data, create a table with all the dates in your data range and do an left join with your data.

   select d.date_day, m.hotel_name, m.price
   
   from all_dates d
   
     left join mydata m on m.arrival_date=d.date_day
   
   where ...

   --
   myDBR Team
4. 

   haigda, Member

   Thanks for the prompt reply.

   This works fine apart from getting a new column in the cross tab where 'null' dates are returned - is there a way round that? I've put the code below.

   Cheers,
   David

   CREATE PROCEDUREsp_DBR_Hotel_stay`()
   BEGIN
   -- Variables
   declare dStart Date;
   declare dEnd Date;
   set dStart = '2015-04-01';
   set dEnd = '2015-10-31';
   If curdate() > dStart then
   Set dStart = Curdate();
   End if;

   select 'dbr.crosstab', 'Hotel';

   select date_format(a.depdate,'%a %d %b') as 'Departure',
   x.hotnam as 'Hotel',
   (x.oneweek + x.sellprice) as 'Price'
   from dt_travel.dt_datelookup a left join
   (Select d.hotnam, d.oneweek, f.sellprice, d.arriv_date from dt_travel.dt_hotelstay d, dt_travel.dt_flightoptions f
   where d.arriv_date > dStart
   and d.hotnts = 7 and d.hotnam <> "Unknown" and d.tourno = f.tourno
   and left(f.descript,3) = "GLA") x on a.depdate = x.arriv_date where a.depdate >= dStart and a.depdate <= dEnd
   Order By a.depdate;

   END
   $$`
5. 

   myDBR Team, Key Master

   You can search a hotel name which exist in the reset set and use that in the date rows where no data exists.

   ifnull( x.hotnam, v_hotelname ) as 'Hotel'

   --
   myDBR Team
6. 

   haigda, Member

   Thanks again,

   I will give that a try.

   Cheers,
   David
7. 

   myDBR Team, Key Master

   As this might sometimes be useful, ee added an option which allows to leave out the NULL data columns. If you run the updater, you can use the option:

   select 'dbr.crosstab.options', 'no_null_data';

   --
   myDBR Team

Reply

You must log in to post.