title: How do I call a User Defined Function to use with select, group by, order by?
body: <p>I have Table1 and I need to get it to look like Table2:</p>
  <h3>Table1</h3>
  <pre><code>VisitingCount |  Date
  -----------------------
        1       |  15:09
        3       |  15:10
        7       |  15:15
        1       |  15:39
        2       |  15:40
        3       |  15:47
  </code></pre>
  <h3>Table2</h3>
  <pre><code>VisitingCount |  Date
  -----------------------------
       11       |  15:00-15:30
       6        |  15:30-16:00
  
   
  </code></pre>
  <p>I wrote a sql user-defined functions like this:</p>
  <pre><code>create FUNCTION [dbo].[fn_GetActivityLogsArranger] (@time AS nvarchar(max))
  RETURNS nvarchar(max)
  AS
  BEGIN
      declare @Return varchar(30)
  
      select @Return = 
          case 
              when @time between '15:00' and '15:30' then '15:00-15:30'
              when @time between '15:30' and '16:00' then '15:30-16:00'
              when @time between '16:00' and '16:30' then '16:00-16:30'
              when @time between '16:00' and '16:30' then '16:00-16:30' 
              when @time between '16:30' and '17:00' then '16:30-17:00' 
              when @time between '17:00' and '17:30' then '17:00-17:30' 
              when @time between '17:30' and '18:00' then '17:30-18:00'
              else 'Unknown'
          end
   
      return @Return
  end
  </code></pre>
  <p>When calling the UDF in my sql query, I achieve the correct result:</p>
  <pre><code>select 
          Count(Page) as VisitingCount,
          dbo.fn_GetActivityLogsArranger(CONVERT(VARCHAR(5),Date, 108))
              as [Time] 
      from 
          scr_SecuristLog     
      where 
          Date between '2009-04-30' and '2009-05-02' AND
          [user] in
          (
              select USERNAME               
              from scr_CustomerAuthorities 
              where customerID = Convert(varchar,4) and ID = Convert(varchar,43) 
          )    
      group by 
          dbo.fn_GetActivityLogsArranger(CONVERT(VARCHAR(5),Date, 108))
      order by 
          dbo.fn_GetActivityLogsArranger(CONVERT(VARCHAR(5),Date, 108)) asc 
  </code></pre>
  <p>But I don't like this method; My dream code would look like this:</p>
  <pre><code>select 
          Count(Page) as VisitingCount,
          dbo.fn_GetActivityLogsArranger(CONVERT(VARCHAR(5),Date, 108))
              as <strong>[TIME]</strong>
      from 
          scr_SecuristLog     
      where 
          Date between '2009-04-30' and '2009-05-02' and 
          user] in
          (
              select USERNAME               
              from scr_CustomerAuthorities 
              where customerID = Convert(varchar,4) and ID = Convert(varchar,43) 
          )    
      group by <strong>[TIME]</strong> 
      order by <strong>[TIME]</strong> asc 
  </code></pre>