sql - 使用多个数据透视表合并我的数据透视表查询中的行

Question

下面的 SQL 查询应该显示教师是否有空。有 4 种可能的预订类型 - 上午、下午、全天和每小时。如果有 AM 预订，单元格中的文本应显示 PM，如果有 PM 预订，则应显示 AM，如果有全天预订，或同时有 AM 和 PM 预订，则应显示“xxx”。每小时预订 - 可以安全地假设每天只有 2 小时预订，上午 1 次(开始时间 <= 中午 12 点)，下午 1 次(结束时间 > 中午 12 点)，这意味着我们应该显示 'xxx '.这一切都很好。

我遇到的问题是在有上午预订然后下午每小时预订，或者下午预订有上午每小时预订时显示“XXX”。

    WITH Bookings AS
    (  SELECT  TeacherID,
            [WeekDay] = DATENAME(WEEKDAY, BookingDate),
            [0], [1], [2], [3],
            [Status] = CASE 
                        WHEN ([0] > 0 AND [1] > 0) THEN 'XXX'
                        WHEN [2] > 0 THEN 'XXX'  
                        WHEN [0] > 0 THEN 'PM'
                        WHEN [1] > 0 THEN 'AM'  
                        WHEN [3] > 0 AND StartTime <= CONVERT(TIME, '12:00:00') AND EndTime >= CONVERT(TIME, '12:00:00') THEN 'XXX'   
                        WHEN [3] > 0 AND EndTime <= CONVERT(TIME, '12:00:00') THEN 'PM'
                        WHEN [3] > 0 AND StartTime >= CONVERT(TIME, '12:00:00') THEN 'AM'  

                        END
    FROM    (   SELECT  TeacherID, 
                        BookingDate, 
                        BookingDuration, 
                        StartTime = CASE WHEN BookingDuration = 3 THEN CAST(MIN(StartTime) OVER(PARTITION BY TeacherID, BookingDate, BookingDuration) AS TIME) ELSE NULL END,
                        EndTime = CASE WHEN BookingDuration = 3 THEN CAST(MAX(EndTime) OVER(PARTITION BY TeacherID, BookingDate, BookingDuration) AS TIME) ELSE NULL END,                        
                        [x] = 1
                FROM    BookingDays 
                WHERE   (Status = 0 OR Status IS NULL)
            ) BookingDays
            PIVOT
            (   SUM(x)
                FOR BookingDuration IN ([0], [1], [2], [3])
            ) pvt

             WHERE BookingDate >= DATEADD(ww, DATEDIFF(ww,0,'05/06/2013'), 0) AND BookingDate <= DATEADD(ww, DATEDIFF(ww,0,'05/06/2013'), 6)

    ), PivotedBookings AS
    (   SELECT  *
    FROM    Bookings
            PIVOT
            (   MAX([Status])
                FOR [WeekDay] IN ([Monday], [Tuesday], [Wednesday], [Thursday], [Friday])
            ) pvt

    )
    SELECT ID,Firstname,Surname,Band,'£' + CONVERT(varchar(50),DefaultChargeRateDaily) + '/' + '£' + CONVERT(varchar(50), DefaultPayRateDaily) as 'BandRates',Telephone,Mobile,Teacher,TeacherAssistant,KeyStage,MAX(Monday) Monday,MAX(Tuesday) Tuesday,MAX(Wednesday) Wednesday,MAX(Thursday) Thursday,MAX(Friday) Friday, Notes
      FROM (
SELECT  t.ID, 
        t.Firstname, 
        t.Surname, 
        tb.Band,
        t.DefaultChargeRateDaily,
        t.DefaultPayRateDaily,
        t.Telephone,
        t.Mobile,
        t.Teacher,
        t.TeacherAssistant,
        CASE WHEN t.Nursery > 0 THEN 'NUR' WHEN t.Reception > 0 THEN 'REC' WHEN t.Year1 > 0 THEN 'Y1' WHEN t.Year2 > 0 THEN 'Y2' WHEN t.Year3 > 0 THEN 'Y3' WHEN t.Year4 > 0 THEN 'Y4' WHEN t.Year5 > 0 THEN 'Y5' WHEN t.Year6 > 0 THEN 'Y6' WHEN t.Year7 > 0 THEN 'Y7' WHEN t.Year8 > 0 THEN 'Y8' WHEN t.Year9 > 0 THEN 'Y9' WHEN t.Year10 > 0 THEN 'Y10' WHEN t.Year11 > 0 THEN 'Y11' WHEN t.ALevel > 0 THEN 'ALevel' END + ' - ' + CASE WHEN t.ALevel > 0 THEN 'ALevel' WHEN t.Year11 > 0 THEN 'Y11' WHEN t.Year10 > 0 THEN 'Y10' WHEN t.Year9 > 0 THEN 'Y9' WHEN t.Year8 > 0 THEN 'Y8' WHEN t.Year7 > 0 THEN 'Y7' WHEN t.Year6 > 0 THEN 'Y6' WHEN t.Year5 > 0 THEN 'Y5' WHEN t.Year4 > 0 THEN 'Y4' WHEN t.Year3 > 0 THEN 'Y3' WHEN t.Year2 > 0 THEN 'Y2' WHEN t.Year1 > 0 THEN 'Y1' WHEN t.Reception > 0 THEN 'REC' WHEN t.Nursery > 0 THEN 'NUR' ELSE '' END as 'KeyStage',

        Monday = CASE WHEN an.Date = DATEADD(ww, DATEDIFF(ww,0,'05/06/2013'), 0) AND an.TeacherID = t.ID THEN an.Text WHEN t.Status = 0 THEN 'XXX'  ELSE COALESCE(pb.Monday, '') END,
        Tuesday = CASE WHEN an.Date = DATEADD(ww, DATEDIFF(ww,0,'05/06/2013'), 1) AND an.TeacherID = t.ID THEN an.Text WHEN t.Status = 0 THEN 'XXX'  ELSE COALESCE(pb.Tuesday, '') END,
        Wednesday = CASE WHEN an.Date = DATEADD(ww, DATEDIFF(ww,0,'05/06/2013'), 2) AND an.TeacherID = t.ID THEN an.Text WHEN t.Status = 0 THEN 'XXX'  ELSE COALESCE(pb.Wednesday, '') END,
        Thursday = CASE WHEN an.Date = DATEADD(ww, DATEDIFF(ww,0,'05/06/2013'), 3) AND an.TeacherID = t.ID THEN an.Text WHEN t.Status = 0 THEN 'XXX'  ELSE COALESCE(pb.Thursday, '') END,
        Friday = CASE WHEN an.Date = DATEADD(ww, DATEDIFF(ww,0,'05/06/2013'), 4) AND an.TeacherID = t.ID THEN an.Text WHEN t.Status = 0 THEN 'XXX'  ELSE COALESCE(pb.Friday, '') END,
        Notes
FROM    Teachers t

        LEFT JOIN PivotedBookings pb
            ON pb.TeacherID = t.ID
        LEFT JOIN TeacherBands tb
            ON tb.ID = t.Band
        LEFT JOIN AvailabilityNotes an 
            ON t.ID = an.TeacherID
            WHERE t.Active = 0 and (t.Status = 1 or t.Status = 0) and t.PrimarySchool = 1 and t.ID = 9094
            ) T1
 GROUP BY ID,Firstname,Surname,Telephone,Mobile,Teacher,TeacherAssistant,KeyStage,Notes,DefaultChargeRateDaily,DefaultPayRateDaily,Band
 ORDER BY Surname,Firstname asc

第一部分生成以下内容 -

SELECT  TeacherID,
            [WeekDay] = DATENAME(WEEKDAY, BookingDate),
            [0], [1], [2], [3],
            [Status] = CASE 
                            WHEN ([0] > 0 AND [1] > 0) THEN 'XXX'
                            WHEN [2] > 0 THEN 'XXX'  
                            WHEN [0] > 0 THEN 'PM'
                            WHEN [1] > 0 THEN 'AM'  
                            WHEN ([3] > 0 AND EndTime <= CONVERT(TIME, '12:00:00')) AND ([0] > 1) THEN 'XXX'
                            WHEN ([3] > 0 AND EndTime <= CONVERT(TIME, '12:00:00')) AND ([1] > 1) THEN 'XXX'
                            WHEN [3] > 0 AND StartTime <= CONVERT(TIME, '12:00:00') AND EndTime >= CONVERT(TIME, '12:00:00') THEN 'XXX'   
                            WHEN [3] > 0 AND EndTime <= CONVERT(TIME, '12:00:00') THEN 'PM'
                            WHEN [3] > 0 AND StartTime >= CONVERT(TIME, '12:00:00') THEN 'AM'                     

                        END
    FROM    (   SELECT  TeacherID, 
                        BookingDate, 
                        BookingDuration, 
                        StartTime = CASE WHEN BookingDuration = 3 THEN CAST(MIN(StartTime) OVER(PARTITION BY TeacherID, BookingDate, BookingDuration) AS TIME) ELSE NULL END,
                        EndTime = CASE WHEN BookingDuration = 3 THEN CAST(MAX(EndTime) OVER(PARTITION BY TeacherID, BookingDate, BookingDuration) AS TIME) ELSE NULL END,                        
                        [x] = 1
                FROM    BookingDays 
                WHERE   (Status = 0 OR Status IS NULL)
            ) BookingDays
            PIVOT
            (   SUM(x)
                FOR BookingDuration IN ([0], [1], [2], [3])
            ) pvt

             WHERE BookingDate >= DATEADD(ww, DATEDIFF(ww,0,'05/06/2013'), 0) AND BookingDate <= DATEADD(ww, DATEDIFF(ww,0,'05/06/2013'), 6)

.

TeacherID | WeekDay  | 0     | 1     | 2     | 3     | Status
9094      | Monday   | NULL  | NULL  | 1     | NULL  | XXX
9094      | Tuesday  | NULL  | NULL  | NULL  | 1     | AM
9094      | Wednesday| NULL  | 1     | NULL  | NULL  | AM
9094      | Thursday | NULL  | 1     | NULL  | NULL  | AM
9094      | Thursday | NULL  | NULL  | NULL  | 1     | PM
9094      | Friday   | NULL  | NULL  | 1     | 1     | XXX

预订时长 -

0 - 上午1 - 下午2 - 全天3 - 每小时

我们可以在这里看到，星期四的两个行需要合并并在状态列中显示为 XXX，而不是单独的 2 行。

下面的屏幕截图显示了一个示例。突出显示为黄色，显示下午，但它应该显示 XXX，因为每小时有下午 1 点和上午。

当有一个小时和一个上午或一个小时和一个下午时，我怎样才能让它显示 XXX？

谢谢!

Answer 1

我会将复杂的逻辑分成两步，使用 IntermediateBookings CTE 将逻辑减少到两列 has_am 和 has_pm。这些更容易计算，并且从这些，最终输出也很容易计算。理论上，您可以改进原始语句的 CASE 表达式以包含更多的 AND 和 OR，但这不是很好可维护的。查询的开头应该是这样的:

WITH IntermediateBooking as (
SELECT  TeacherID,
            [WeekDay] = DATENAME(WEEKDAY, BookingDate),
            [0], [1], [2], [3],
            has_am = case when 
                          [1] > 0
                       OR [2] > 0
                       OR ([3] > 0 AND StartTime <= CONVERT(TIME, '12:00:00'))
                     then 1
                     else null
                     end,
            has_pm = case when 
                          [0] > 0
                       OR [2] > 0
                      OR ([3] > 0 AND EndTime >= CONVERT(TIME, '12:00:00'))
                     then 1
                     else null
                     end
    FROM    (   SELECT  TeacherID, 
                        BookingDate, 
                        BookingDuration, 
                        StartTime = CASE WHEN BookingDuration = 3 THEN CAST(MIN(StartTime) OVER(PARTITION BY TeacherID, BookingDate, BookingDuration) AS TIME) ELSE NULL END,
                        EndTime = CASE WHEN BookingDuration = 3 THEN CAST(MAX(EndTime) OVER(PARTITION BY TeacherID, BookingDate, BookingDuration) AS TIME) ELSE NULL END,                        
                        [x] = 1
                FROM    BookingDays 
                WHERE   (Status = 0 OR Status IS NULL)
            ) BookingDays
            PIVOT
            (   SUM(x)
                FOR BookingDuration IN ([0], [1], [2], [3])
            ) pvt

             WHERE BookingDate >= DATEADD(ww, DATEDIFF(ww,0,'05/06/2013'), 0) AND BookingDate <= DATEADD(ww, DATEDIFF(ww,0,'05/06/2013'), 6)
),
Bookings AS (
SELECT TeacherID, [WeekDay],
       case when sum(has_am) > 0 and sum(has_pm) > 0 then 'XXX'
            WHEN sum(has_am) > 0 then 'AM'
            WHEN sum(has_pm) > 0 then 'PM'
       end as [Status]
  FROM IntermediateBookings
GROUP BY TeacherID, [WeekDay]
)

其余的 - 从 PivotedBookings 开始 - 可以保持原样。

但是，由于我没有可用的表格，我无法检查语法错误以及结果是否符合要求。可能需要进行一些调整。