J'ai l'instruction SQL suivante qui renvoie 2 lignes (jours de réservation)
SELECT bd.ID, t.FirstName, t.Surname, CASE WHEN bd.BookingDuration = 3 AND CONVERT(time(0), bd.StartTime) < CONVERT(time(0), '12:00:00') AND bd.NoOfHOurs < 5.5 THEN bd.ID ELSE NULL END as 'TuesdayHourlyAM', CASE WHEN bd.BookingDuration = 3 AND CONVERT(time(0), bd.StartTime) < CONVERT(time(0), '12:00:00') AND bd.NoOfHOurs < 5.5 THEN bd.ID ELSE NULL END as 'TuesdayHourlyAM2' from BookingDays bd join ( select ID, MIN(StartTime) as minx, MAX(StartTime) as maxx from BookingDays where BookingDate = CONVERT(date, '18/06/2013', 103) and BookingType = 0 group by ID ) tmin on bd.ID = tmin.ID and bd.StartTime = tmin.minx inner join Teachers t on bd.TeacherID = t.ID where t.Surname = 'cairns' group by bd.ID, bd.StartTime, bd.DayText, t.Firstname, t.Surname, bd.BookingDate, bd.BookingDuration, bd.NoOfHours, tmin.minx, tmin.maxx
Cela revient –
Ce que je cherche est une table avec un format similaire, mais en 1 ligne:
Prénom | Nom | MardiHourlyAM1 | TuesdayHourlyAM1Start | MardiHourlyAM1End | MardiHourlyAM2 | TuesdayHourlyAM2Start | MardiHourlyAM2End
TuesdayHourlyAM1: BookingDayID TuesdayHourlyAM2: BookingDayID Début / Fin: heures de début et de fin des réservations
Où AM1 est la plus petite heure de début et AM2 est la plus grande heure de début (il n'y aura jamais plus de 2 jours de réservation pour ce critère).
SELECT t.FirstName, t.Surname, tmin.id as TuesdayHourlyAM1, tmin.StartTime as TuesdayHourlyAM1Start, tmin.Endtime as TuesdayHourlyAM1End , tmax.id as TuesdayHourlyAM2, tmax.StartTime as TuesdayHourlyAM2Start, tmax.Endtime as TuesdayHourlyAM2End Teachers t inner join from ( select top 1 id, bd.teacherID MIN(StartTime) as StartTime, endtime as Endtime from BookingDays bd inner join Teachers t on bd.TeacherID = t.ID where BookingDate = CONVERT(date, '18/06/2013', 103) and BookingType = 0 and t.Surname = 'cairns' group by id,endtime, bd.teacherID order by StartTime asc ) tmin on t.id = tmin.teacherID join ( select top 1 id, bd.teacherID, max(StartTime) as StartTime, endtime as Endtime from BookingDays bd inner join Teachers t on bd.TeacherID = t.ID where BookingDate = CONVERT(date, '18/06/2013', 103) and BookingType = 0 and t.Surname = 'cairns' group by id,endtime, bd.teacherID order by StartTime desc ) tmax on t.id = tmax.teacherID
Essayez de regrouper votre sous-requête par enseignant plutôt que par réservation
SELECT t.FirstName, t.Surname, minx as TuesdayHourlyAM1, maxx as TuesdayHourlyAM2 from BookingDays bd join ( select TeacherID, MIN(StartTime) as minx, MAX(StartTime) as maxx from BookingDays where BookingDate = CONVERT(date, '18/06/2013', 103) and BookingType = 0 group by TeacherID ) tmin on bd.teacherID = tmin.ID and bd.StartTime = tmin.minx inner join Teachers t on bd.TeacherID = t.ID where t.Surname = 'cairns'