How to get the MAX(Hour) and The MIN(Hour) for each day in this query?

Question

I have this structure of table Diary:

CREATE TABLE Diary
(
     [IdDiary] bigint, 
     [IdDay] numeric(18,0)
);

INSERT INTO Diary ([IdDiary], [IdDay])
values 
(51, 1),
(52, 2),
(53, 5);

And this other structure for table DiaryTimetable:

  CREATE TABLE DiaryTimetable
(
     [IdDiary] bigint, 
     [Hour] varchar(50)
);

  INSERT INTO DiaryTimetable ([IdDiary], [Hour])
VALUES
    (51, '09:00'),
    (51, '09:30'),
    (51, '10:00'),
    (51, '10:30'),
    (51, '11:00'),
    (51, '11:30'),
    (52, '11:00'),
    (52, '11:30'),
    (52, '12:00'),
    (52, '12:30'),
    (52, '13:00'),
    (52, '13:30'),
    (53, '15:00'),
    (53, '15:30'),
    (53, '16:00'),
    (53, '16:30');

The table Diary contains an IdDiary and the IdDay is the number of day, for example:

Monday --> 1
Tuesday --> 2
Wednesday --> 3
Thursday --> 4
Friday --> 5
Saturday --> 6
Sunday --> 7

The table DiaryTimetable contains the iddiary, and the hour. I want want to get the max hour and the min hour in the table DiaryTimetable for each day appears in the Diary table, If I put this query the result will be only the max hour and the min hour for all the query:

select MAX(Hour), MIN(Hour) from DiaryTimetable
inner join Diary on
DiaryTimetable.IdDiary = Diary.IdDiary

The result for wat I need will be something like that:

IdDiary  IdDay   Min Hour   Max Hour
-----    -----   --------   ---------
51        1      09:00       11:30
52        2      11:00       13:30
53        5      15:00       16:30

How can I get this, thanks?

SQL FIDDLE DEMO HERE

Answer 1

You use a GROUP BY clause:

SELECT d.IdDiary, d.IdDay, MIN(Hour), MAX(Hour)
FROM Diary AS d
LEFT JOIN DiaryTimetable AS dt ON d.IdDiary = dt.IdDiary
GROUP BY d.IdDiary, d.IdDay

Demo here