title: SQL alternative to current use of cursor and temp tables
body: <p>I am going through cleaning up some stored procedures left by prior employees and came across one where I am trying to determine if there is a way it can be handled without the use of a cursor. Currently the cursor looks at a temp table that has a start and end date for each record. It then takes the ID of the record and creates a row with the ID and date for each date that falls in the range of start &amp; end date. </p>
  
  <p>This data is then used create another temp table that stores the ID of the record and the count of distinct dates for each ID. </p>
  
  <p>Could I not just do a <code>datediff</code> for the date part days between the start and end dates? I feel like there must be a much better way to derive this information but I'm worried that I may be oversimplifying it and missing something. </p>
  
  <p>Here is the code as it stands now:</p>
  
  <pre><code>declare @StartDate datetime, @EndDate datetime, @ID bigint, @WSIdAndDates cursor 
  
  set @WSIdAndDates = cursor fast_forward for
  select servicestartdate, serviceenddate, WorksheetID from #que_rp_claims_dates 
  
  open @WSIdAndDates
  fetch next from @WSIdAndDates into @StartDate, @EndDate, @ID 
  while @@FETCH_STATUS = 0
  begin
  
          with ctedaterange 
              as (select [Dates]=@StartDate, 
                  @ID[workSheetid]
  
              union ALL
  
              select [dates] + 1,
                  @ID[workSheetid]
              from ctedaterange 
              where [dates] + 1 &lt;= @EndDate) 
  
              insert into #tempworksheetidanddates
              select [workSheetid],
                  [dates] 
              from ctedaterange 
          option(maxrecursion 0)
  
      fetch next from @WSIdAndDates into @StartDate, @EndDate, @ID 
  end
  
  close @WSIdAndDates
  deallocate @WSIdAndDates
  
  select worksheetid, count(distinct(dates)) as LOS into ##que_rp_claims_LOS 
  from #tempworksheetidanddates
  group by worksheetid
  </code></pre>
  
  <p>The table <code>##que_rp_claims_LOS</code> is the only one that gets used outside of this snippet. The other temp table is dropped. </p>
  
  <p>Any insight or advice would be greatly appreciated.</p>