Help with cross join creating a week view calendar

Hi all i have a table called bookings like this ( Ive bolded the columns to align)

CustID  VenueID BookingDt                   Session

45      44      2010-03-20 00:00:00.000     PM      
45      44      2010-03-27 00:00:00.000     PM      
45      44      2009-10-18 00:00:00.000     PM          
45      44      2009-10-24 00:00:00.000     PM

I have another table called Venues

oID oLocation   oPitch

1   Left Park   Rugby
2   Right Park  Rugby

The tables are inter joined by Venues.oID=bookings .Cu开发者_如何学运维stID

i want to make a table such as this

X Column = week days Y column = locations

oID  oSun oMon oTue oWed oThu oFri oSat

1    x          x    x         x
2    x    x          x    x    x   x x

I believe i have to do a cross join with the data from the bookings Database

Eg

select distinct v.olocation , b.BookingDt from oVenue V 
cross join tblBookings B
Where B.VenueID=V.oID
and  DATEPART( wk, b.BookingDt )='44' 
and  DATEPART( yy, b.BookingDt )='2009' 

But this does oID and Date, i want it to do check to see if that date is there, if so place a x in its place other wise place a '' in its place.

Not sure the best way to proceed. Any help is muchly appreciated.

Thanks in advance

Since you already restricted the week and year in your query, this is how to display it:

select
  v.olocation,
  max(case DATEPART(weekday, b.BookingDt) When 1 then 'x' else '' end) Sun,
  max(case DATEPART(weekday, b.BookingDt) When 2 then 'x' else '' end) Mon,
  max(case DATEPART(weekday, b.BookingDt) When 3 then 'x' else '' end) Tue,
  max(case DATEPART(weekday, b.BookingDt) When 4 then 'x' else '' end) Wed,
  max(case DATEPART(weekday, b.BookingDt) When 5 then 'x' else '' end) Thu,
  max(case DATEPART(weekday, b.BookingDt) When 6 then 'x' else '' end) Fri,
  max(case DATEPART(weekday, b.BookingDt) When 7 then 'x' else '' end) Sat
from
(
    select distinct v.olocation , b.BookingDt 
    from oVenue V 
    LEFT JOIN tblBookings B on B.VenueID=V.oID
    and  DATEPART( wk, b.BookingDt )='44' 
    and  DATEPART( yy, b.BookingDt )='2009' 
) selweek
group by v.olocation

How the data is displayed should be a front-end issue, not a database issue. I wouldn't concentrate on things like putting "x" in a specific spot. Return the data that your application needs to fill in your calendar and have the front-end do that.

That said, in order to create results like what you're looking for, you're missing a set of data - the set of calendar days. You can do this with a temporary table, a CTE, or a permanent table in your database, but you basically need a table that gives you all of the days in question as a resultset. You can then LEFT OUTER JOIN from that table to your bookings table and use CASE to fill in values based on whether or not a matched bookings row was found.