帮助交叉联接创建周视图日历

发布于 2024-12-05 08:32:10 字数 1063 浏览 1 评论 0原文

大家好,我有一个名为 bookings 的表,如下所示(我已将列加粗以对齐)

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

我有另一个名为 Venues 的表,

oID oLocation   oPitch

1   Left Park   Rugby
2   Right Park  Rugby

这些表由 Venues.oID=bookings .CustID 相互连接,

我想制作一个像这样的表

X Column = week天 Y 列 = 位置

oID  oSun oMon oTue oWed oThu oFri oSat

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

我相信我必须与预订数据库中的数据进行交叉连接,

例如,

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' 

但这会执行 oID 和日期,我希望它检查该日期是否存在,如果存在,则将 ax 放在其他位置明智的做法是在其位置放置一个“”。

不确定继续的最佳方法。 非常感谢任何帮助。

提前致谢

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 .CustID

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

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

你的背包 2024-12-12 08:32:10

由于您已经限制了查询中的周和年,因此显示它的方法如下:

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

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
穿越时光隧道 2024-12-12 08:32:10

数据如何展示应该是前端的问题,而不是数据库的问题。我不会专注于诸如将“x”放在特定位置之类的事情。返回您的应用程序需要填写日历的数据并让前端执行此操作。

也就是说,为了创建您正在寻找的结果,您缺少一组数据 - 日历天数集。您可以使用数据库中的临时表、CTE 或永久表来执行此操作,但您基本上需要一个表来提供所有相关日期作为结果集。然后,您可以从该表到您的预订表进行LEFT OUTER JOIN,并根据是否找到匹配的预订行使用CASE 填充值。

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.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文