帮助交叉联接创建周视图日历
大家好,我有一个名为 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 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
由于您已经限制了查询中的周和年,因此显示它的方法如下:
Since you already restricted the week and year in your query, this is how to display it:
数据如何展示应该是前端的问题,而不是数据库的问题。我不会专注于诸如将“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 useCASE
to fill in values based on whether or not a matched bookings row was found.