title: How to get the MAX(Hour) and The MIN(Hour) for each day in this query?
body: <p>I have this structure of table Diary:</p>
  
  <pre><code>CREATE TABLE Diary
  (
       [IdDiary] bigint, 
       [IdDay] numeric(18,0)
  );
  
  INSERT INTO Diary ([IdDiary], [IdDay])
  values 
  (51, 1),
  (52, 2),
  (53, 5);
  </code></pre>
  
  <p>And this other structure for table DiaryTimetable:</p>
  
  <pre><code>  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');
  </code></pre>
  
  <p>The table Diary contains an IdDiary and the IdDay is the number of day, for example:</p>
  
  <pre><code>Monday --&gt; 1
  Tuesday --&gt; 2
  Wednesday --&gt; 3
  Thursday --&gt; 4
  Friday --&gt; 5
  Saturday --&gt; 6
  Sunday --&gt; 7
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>select MAX(Hour), MIN(Hour) from DiaryTimetable
  inner join Diary on
  DiaryTimetable.IdDiary = Diary.IdDiary
  </code></pre>
  
  <p>The result for wat I need will be something like that:</p>
  
  <pre><code>IdDiary  IdDay   Min Hour   Max Hour
  -----    -----   --------   ---------
  51        1      09:00       11:30
  52        2      11:00       13:30
  53        5      15:00       16:30
  </code></pre>
  
  <p>How can I get this, thanks?</p>
  
  <p><a href="http://sqlfiddle.com/#!6/5ca9c9" rel="nofollow">SQL FIDDLE DEMO HERE</a></p>