title: How to show Time interval in sql server
body: <p>i have two variable where time range is stored. 
  and i have to display time interval in this format</p>
  
  <pre><code>09:00:00  -- 09:30:00
  09:30:00  -- 10:00:00
  10:00:00  -- 10:30:00
  10:30:00  -- 11:00:00
  
  UpTO
  
  05:00:00 -- 05:30:00
  </code></pre>
  
  <h2>here is my code</h2>
  
  <pre><code>DECLARE @StartTime DATETIME
  DECLARE @InvTime DATETIME
  DECLARE @EndTime DATETIME
  
  SET @StartTime='09:00:00'
  SET @EndTime='17:30:00'
  
  WHILE @StartTime &lt; @EndTime
  BEGIN
  
      SET @InvTime = DATEADD(MINUTE, 30, @StartTime)
  
      PRINT CONVERT(char(8), @StartTime, 108)+ '  ' + CONVERT(char(8), @InvTime, 108) 
      SET @StartTime = DATEADD(MINUTE, 30, @StartTime)
  END
  </code></pre>
  
  <p>anyway my code is working but looking for suggestion to achieve it by any other best way.</p>
  
  <h2>My Job done. Full sql as follows</h2>
  
  <pre><code>DECLARE     @StartTime datetime = '2015-06-09 09:00:00',
              @EndTime datetime = '2015-06-09 18:00:00',
              @Interval int = 30 -- this can be changed.
  
  DECLARE     @PartyIntervals TABLE (
              Party1Name      VARCHAR(500),
              StartRange      DATETIME,
              EndRange        DATETIME) 
  
  DECLARE     @CSVIntervals   TABLE (
              Party1Name      VARCHAR(500),
              Incoming        INT,
              StartRange      DATETIME) 
  
  ;WITH cSequence AS
  (
      SELECT
         @StartTime AS StartRange, 
         DATEADD(MINUTE, @Interval, @StartTime) AS EndRange
      UNION ALL
      SELECT
        EndRange, 
        DATEADD(MINUTE, @Interval, EndRange)
      FROM cSequence 
      WHERE DATEADD(MINUTE, @Interval, EndRange) &lt; @EndTime
  )
  
  
  INSERT INTO @PartyIntervals
  SELECT      Party1Name, 
              StartRange,
              EndRange 
  FROM (SELECT DISTINCT Party1Name FROM tridip_Interval) A, cSequence ORDER BY Party1Name;
  --SELECT * FROM @PartyIntervals ORDER BY Party1Name
  
  INSERT INTO @CSVIntervals
  SELECT  Party1Name,
          count(*) as incoming,
          CONVERT(smalldatetime, ROUND(CAST(Convert(datetime, [Call Start]) AS float) * 48.0,0,1)/48.0) AS StartRange
  FROM    tridip_Interval
  WHERE   direction='I'
  GROUP   BY CONVERT(smalldatetime, ROUND(CAST(Convert(datetime, [Call Start]) AS float) * 48.0,0,1)/48.0), Party1Name
  
  SELECT  P.Party1Name,
          P.StartRange,
          P.EndRange,
          IsNull(Incoming , 0) AS Incoming
  FROM    @PartyIntervals P LEFT OUTER JOIN 
          @CSVIntervals A ON  A.Party1Name = P.Party1Name AND P.StartRange = A.StartRange
  ORDER BY  P.Party1Name,P.StartRange
  </code></pre>