title: SQL statement to calculate an Hourly amount
body: <p>I need to calculate the amount of a resource consumed by hour. For example, consider phone call minutes.  For the following records, I’d like to generate the following results:  </p>
  
  <pre><code>Call_ID   StartDateTime     EndDateTime  
  1         3/15/2014 11:25   3/15/2014 14:01  
  2         3/15/2014 11:50   3/15/2014 13:10  
  3         3/15/2014 12:05   3/15/2014 12:55  
  4         3/15/2014 13:04   3/15/2014 15:02  
  5         3/15/2014 13:15   3/15/2014 14:22  
  6         3/15/2014 14:35   3/15/2014 15:18  
  7         3/15/2014 15:10   3/15/2014 15:29  
  
  Date        Hour    CallMinutes  
  3/15/2014   11       45  
  3/15/2014   12       170  
  3/15/2014   13       171  
  3/15/2014   14       108  
  3/15/2014   15       39  
  </code></pre>
  
  <p>I have SQL that will do this, but it uses a cursor and I would like to find a faster alternative. Here’s the working code I have now:</p>
  
  <pre><code>/* create &amp; fill the Inputs table */  
  CREATE TABLE PhoneCalls(  
      Call_ID         int IDENTITY(1,1) NOT NULL,  
      StartDateTime   datetime NOT NULL,  
      EndDateTime     datetime NOT NULL,  
   CONSTRAINT PK_PhoneCalls PRIMARY KEY CLUSTERED ( Call_ID ASC) ON [PRIMARY]  
  ) ON [PRIMARY];  
  
  INSERT INTO PhoneCalls (StartDateTime, EndDateTime) VALUES ('3/15/2014 11:25', '3/15/2014 14:01');  
  INSERT INTO PhoneCalls (StartDateTime, EndDateTime) VALUES ('3/15/2014 11:50', '3/15/2014 13:10');  
  INSERT INTO PhoneCalls (StartDateTime, EndDateTime) VALUES ('3/15/2014 12:05','3/15/2014 12:55');  
  INSERT INTO PhoneCalls (StartDateTime, EndDateTime) VALUES ('3/15/2014 13:04','3/15/2014 15:02');  
  INSERT INTO PhoneCalls (StartDateTime, EndDateTime) VALUES ('3/15/2014 13:15','3/15/2014 14:22');  
  INSERT INTO PhoneCalls (StartDateTime, EndDateTime) VALUES ('3/15/2014 14:35','3/15/2014 15:18');  
  INSERT INTO PhoneCalls (StartDateTime, EndDateTime) VALUES ('3/15/2014 15:10','3/15/2014 15:29');  
  
  /* Create the Temp table to hold the intermediate results */
  Create Table #HourlyCallTimes (
      Call_ID     int NOT NULL,
      CallDate    date NOT NULL,
      CallHour    smallint NOT NULL,
      CallTime    int NOT NULL);
  
  /* Determine Hourly totals of CallTime using a cursor */
  SET NOCOUNT ON;
  DECLARE @Call_ID int, @StartDt datetime, @EndDt datetime, @CallLength int, @Hour int, @CallTime int;
  declare CallsCursor cursor for
      select Call_ID, StartDateTime, EndDateTime, DATEDIFF(minute, StartDateTime, EndDateTime) as CallLength
      FROM PhoneCalls
  open CallsCursor
  fetch next from CallsCursor into @Call_ID, @StartDt, @EndDt, @CallLength
  while @@FETCH_STATUS = 0
  begin
      /* make sure that the call was at least a minute long */
      IF (@CallLength &gt; 0.01)
      BEGIN
          -- Record the call time for the first partial hour
          SET @Hour = DATEPART(Hour, @StartDt)
          SET @CallTime = 60 - DATEPART(Minute, @StartDt)
          if (@CallLength &lt; @CallTime) -- check if this us the last (partial) hour
              SET @CallTime = @CallLength
          INSERT INTO #HourlyCallTimes (Call_ID, CallDate, CallHour, CallTime)
              VALUES (@Call_ID, CAST(@StartDt as DATE), @Hour, @CallTime)
  
          -- move to the beginning of the next hour
          SET @StartDt = DATEADD(HOUR,@Hour+1,CAST(CAST(@StartDt as DATE) as DateTime))
          SET @CallLength = @CallLength - @CallTime
          While @StartDt &lt; @EndDt
          BEGIN
              SET @Hour = DATEPART(Hour, @StartDt)
              SET @CallTime = 60;
              if (@CallLength &lt; @CallTime)  -- check if this is the last (partial) hour
                  SET @CallTime = @CallLength
          INSERT INTO #HourlyCallTimes (Call_ID, CallDate, CallHour, CallTime)
              VALUES (@Call_ID, CAST(@StartDt as DATE), @Hour, @CallTime)
              -- move to the beginning of the next hour
              SET @StartDt = DATEADD(Hour,1,@StartDt)     
              SET @CallLength = @CallLength - @CallTime
          END
      END
      fetch next from CallsCursor into @Call_ID, @StartDt, @EndDt, @CallLength
  END
  close CallsCursor;
  deallocate CallsCursor;
  
  /* here's the final results table */
  Create Table HourlyCallTotals (
      CallDate    date NOT NULL,
      CallHour    smallint NOT NULL,
      CallTime    int NOT NULL);
  
  Insert into HourlyCallTotals (CallDate, CallHour, CallTime)
      select CallDate, CallHour, SUM(CallTime) from #HourlyCallTimes group by CallDate, CallHour;
  
  DROP TABLE #HourlyCallTimes;
  DROP TABLE PhoneCalls;
  
  SELECT * FROM HourlyCallTotals;
  DROP TABLE HourlyCallTotals;
  
  /* expected Results */
  /*  CallDate    CallHour    CallTime
      3/15/2014       11          45
      3/15/2014       12          170
      3/15/2014       13          171
      3/15/2014       14          108
      3/15/2014       15          39 */
  </code></pre>