SQL alternative to current use of cursor and temp tables

Question

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 & end date.

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.

Could I not just do a datediff 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.

Here is the code as it stands now:

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 <= @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

The table ##que_rp_claims_LOS is the only one that gets used outside of this snippet. The other temp table is dropped.

Any insight or advice would be greatly appreciated.

Answer 1

The process is taking the ID and a start and end date from #que_rp_claims_dates and opens a cursor for this.

With each row the same is done: A recursive CTE is collecting/calculating all days from the start date up to the end date and fill this in a table.

In the final step you deliver nothing more than the ID and the count of days.

So I think you are right... If your intermediate results (the temp tables) are not needed anywhere else it should suffice to calculate the DATEDIFF for each ID.

Attention

If there are DATETIME values in use the result might depend on the actual time (if the times are not full day). Look at this especially carefully!

And btw: I appreciate your efforts to get rid of bad stuctures!

Answer 2

A better alternative to temporary tables is using table variables. You can declare a table variable like this:

DECLARE @tempVariable table(
    WorksheetID int,
    servicestartdate DATE,
    serviceenddate DATE
);

And as far as the cursor is concerned, you can always insert all you data in a table variable, use a while to iterate through it and delete the entries just after you have handled them. Something like this:

while exists (select top 1 * from tempVariable)
begin
    select top 1 @StartDate = servicestartdate, @EndDate = serviceenddate, @ID = WorksheetID from @tempVariable

    //your code

    DELETE TOP(1) FROM @tempVariable;
end