我有下面的SQL语句,它可以工作。它会发现房间什么时候有空。我现在唯一的问题是需要将p.DayShort
转换为日期值。DayShort
将返回S、M、Tu、W、Th、F、Sa。我有一个名为week_beginning
的变量,它是星期日的日期,所以我们假设值为2015年11月8日,我希望每行数据都返回2015年8月11日的一个日期,所以如果DayShort是W,我希望它返回2015年11日(基本上是week_beginning变量的+3)。有人教我怎么做吗?
SELECT
p.DayShort, p.PeriodNumber, i.StartTime, i.EndTime
FROM
PeriodList AS p
LEFT JOIN
PeriodInformation AS i ON p.pk_PeriodListID = i.fk_PeriodListID
LEFT JOIN
Lesson AS l ON l.fk_PeriodListID = i.fk_PeriodListID
LEFT JOIN
LessonRoom a ON l.pk_LessonID = a.fk_LessonID
LEFT JOIN
RoomList r ON a.fk_RoomID = r.pk_RoomID
WHERE
r.RoomCode = "SportsHall"
AND p.PeriodNumber NOT IN ('"GAM/J","ACT,J","7E","8E","6E","5E"')
AND i.Rotation = "Sum"
这个怎么样?
SELECT DATEADD('d', p.Offset, week_beginning) as thedate, p.DayShort, p.PeriodNumber, i.StartTime, i.EndTime
FROM (
SELECT PeriodList.*,
CASE
WHEN DayShort = 'M' THEN 1
WHEN DayShort = 'Tu' THEN 2
WHEN DayShort = 'W' THEN 3
WHEN DayShort = 'Th' THEN 4
WHEN DayShort = 'F' THEN 5
WHEN DayShort = 'Sa' THEN 6
ELSE 0
END as Offset
FROM PeriodList
) AS p
-- no change for the rest of the query
请注意,这里的DATEADD是它在SQL Server中的存在方式——其他风格的SQL可能有所不同。