可替代MIN选择多个行

The below SQL query is supposed to show a teacher availability. There are 3 possible booking types - AM, PM or Full Day. If theres an AM booking, the text in the cell should show PM, if theres a PM booking, it should show AM, if theres a full day booking, or both an AM and a PM booking, it should show 'xxx'.

下面的SQL查询应该显示教师可用性。有三种可能的预订类型——上午、下午或全天。如果有AM预订，手机中的文字应该显示PM，如果有PM预订，应该显示AM，如果有全天预订，或者AM和PM预订，应该显示xxx。

This all works fine when theres 1 AM, 1 PM or 1 Full Day booking, but if theres 1 AM and 1 PM on the same day, it wont show XXX because I am using the MIN(). How can I get it to evaluate all bookings in one day, rather than the smallest one? (You can see I have tried to show what I mean on the Thursday section of code.

这在凌晨1点、下午1点或全天预订时都可以，但如果当天上午1点和下午1点，它不会显示XXX，因为我正在使用MIN()。我如何能在一天内评估所有的预订，而不是最小的预订?(你可以看到，我已经试过在周四的代码部分展示我的意思。

SQL:

with CTE_D as 
( 
   SELECT 
      DATEADD(ww, DATEDIFF(ww,0,GETDATE()), 0) as BookingDate 

   UNION ALL

   SELECT
      DATEADD(day, 1, BookingDate) 
   FROM
      CTE_D 
   WHERE
      BookingDate < DATEADD(ww, DATEDIFF(ww,0,GETDATE()), 6) 
)
SELECT 
    t.ID, t.Firstname, 
    t.Surname, tb.Band, t.Telephone, t.Mobile, t.Teacher, t.TeacherAssistant, t.PrimarySchool, t.SecondarySchool,
    MIN(CASE WHEN bd.DayText = 'Monday' AND bd.BookingDuration = 0 THEN 'PM' ELSE 'Full Day' END) "Monday", 
    MIN(CASE WHEN bd.DayText = 'Tuesday' AND bd.BookingDuration = 0 THEN 'PM' ELSE 'Full Day' END) "Tuesday", 
    MIN(CASE WHEN bd.DayText = 'Wednesday' AND bd.BookingDuration = 0 THEN 'PM' ELSE 'Full Day' END) "Wednesday", 
    MIN(CASE WHEN bd.DayText = 'Thursday' AND bd.BookingDuration = 0 THEN 'PM' WHEN bd.DayText = 'Thursday' AND bd.BookingDuration = 1 THEN 'AM' WHEN bd.DayText = 'Thursday' AND bd.BookingDuration = 2 or (bd.BookingDuration = 1 and bd.BookingDuration = 0) THEN 'xxx' END) "Thursday", 
    MIN(CASE WHEN bd.DayText = 'Friday' AND bd.BookingDuration = 0 THEN 'PM' ELSE 'Full Day' END) "Friday",
    Notes 
        FROM Teachers t 
        cross join CTE_D d 
        left join BookingDays bd 
            on t.ID = bd.TeacherID and 
               bd.BookingDate = d.BookingDate 
        left join BookingDurations bds 
            on bd.BookingDuration = bds.ID 
        left join TeacherBands tb on t.Band = tb.ID
        WHERE t.Active = 0 and (t.Status = 0 or t.Status = 1) and (bd.Status = 0 or bd.Status IS NULL) and PrimarySchool = 1
        GROUP BY Firstname, Surname, t.Telephone, t.Mobile, t.Notes, tb.Band, t.ID, t.Teacher, t.TeacherAssistant, t.PrimarySchool, t.SecondarySchool, t.Nursery, t.Reception, t.Year1, t.Year2, t.Year3, t.Year4, t.Year5, t.Year6, t.Year7, t.Year8, t.Year9, t.Year10, t.Year11, t.ALevel
        ORDER BY Surname, Firstname ASC

Table columns:

表列:

ID | Firstname | Surname | Band | Telephone | Mobile | Teacher | Teacher Assistant | PrimarySchool | SecondarySchool | KeyStage | Mon | Tues | Wed | Thurs | Fri | Notes

Thanks, Matt

谢谢,马特

1 个解决方案

#1

I think the problem is you are trying to perform 2 operations at the same time that need to be separate (check for the weekday, and check for the booking duration). To do this I have assumed your booking Durations to be :

我认为问题是您正在尝试同时执行两个需要分开的操作(查看工作日，查看预订时间)。为此，我假设您的预订时间为:

0 - PM
0 -点
1 - AM
1 -是
2 - Full Day
2 -天

If this is not correct you'll need to tweak my query slighlty, but this should get you on the right track. I've also assumed no record in the booking table means not available:

如果这不是正确的，您将需要稍微调整我的查询，但这将使您走上正确的轨道。我还假设预订表中没有记录意味着无法获得:

SET LANGUAGE British;

WITH Bookings AS
(   SELECT  BookingDays.TeacherID,
            [WeekDay] = DATENAME(WEEKDAY, BookingDate),
            [Status] = CASE WHEN BookingDuration = 0 THEN 'PM' WHEN BookingDuration = 1 THEN 'AM' ELSE 'Full Day' END
    FROM    BookingDays
), PivotedBookings AS
(   SELECT  *
    FROM    Bookings
            PIVOT
            (   MAX([Status])
                FOR [WeekDay] IN ([Monday], [Tuesday], [Wednesday], [Thursday], [Friday])
            ) pvt
)
SELECT  t.ID, 
        t.Firstname, 
        t.Surname, 
        tb.Band, 
        t.Telephone, 
        t.Mobile, 
        t.Teacher, 
        t.TeacherAssistant, 
        t.PrimarySchool, 
        t.SecondarySchool,
        Monday = COALESCE(pb.Monday, 'Not Available'),
        Tuesday = COALESCE(pb.Tuesday, 'Not Available'),
        Wednesday = COALESCE(pb.Wednesday, 'Not Available'),
        Thursday = COALESCE(pb.Thursday, 'Not Available'),
        Friday = COALESCE(pb.Friday, 'Not Available'),
        t.Notes
FROM    Teachers t
        LEFT JOIN PivotedBookings pb
            ON pb.TeacherID = t.ID
        LEFT JOIN TeacherBands tb
            ON tb.ID = t.Band;

Cut down example on SQL Fiddle

在SQL Fiddle上减少示例。

ADDENDUM

齿顶高

Having read this line:

读完这条线:

WHEN bd.DayText = 'Thursday' AND bd.BookingDuration = 2 or (bd.BookingDuration = 1 and bd.BookingDuration = 0)

It leads me to believe that there are 2 possibilities for full day, either a 2 in booking duration, or an AM and a PM entry for the same teacher and date in the table. In which case it is necessary to pivot the data twice, so your first CTE becomes:

这让我相信，全天有两种可能，一种是预订时间，另一种是AM，一种是同一位老师的PM条目，以及表格中的日期。在这种情况下，需要对数据进行两次旋转，所以您的第一个CTE变成:

WITH Bookings AS
(   SELECT  BookingDays.TeacherID,
            [WeekDay] = DATENAME(WEEKDAY, BookingDate),
            [Status] = CASE WHEN [2] > 0 THEN 'Full Day'
                            WHEN [0] > 0 AND [1] > 0 THEN 'xxx'
                            WHEN [0] > 0 THEN 'PM'
                            WHEN [1] > 0 THEN 'AM'
                        END

    FROM    (   SELECT  TeacherID, BookingDate, BookingDuration, [X] = 1
                FROM    BookingDays
            ) BookingDays
            PIVOT
            (   SUM(X)
                FOR BookingDuration IN ([0], [1], [2])
            ) pvt
)

Updated example on SQL Fiddle (Note Thursday for Teacher 1)

SQL Fiddle最新的示例(教师1星期四)

#1