I have inherited a proc that is causing the DBAs headaches. Specifically, there is a portion of the code that declares variables and then loops through a staging table while setting a counter.
I would like to rewrite this section to be more efficient, but don't have much experience with these sorts of things; could a CROSS APPLY work in this scenario? What is the "best practice" for this sort of thing?
Here's the code:
DECLARE @MAXMONTHS INT
@MAXMONTHS = MAX(DATEDIFF(MM,STARTDATE,ENDDATE)) + 1
[Stage].[RESULTS] -- ADD 1 IN ORDER TO ACCOUNT FOR ROUNDING DOWN IN YEAR CALCULATION
DECLARE @THISMONTH INT
SET @THISMONTH = 1
WHILE (@THISMONTH <= @MAXMONTHS)
, @THISMONTH AS Phase
, DATEADD(MM,@THISMONTH-1,R.STARTDATE) AS STARTDATE
, DATEADD(D,-1,DATEADD(MM,@THISMONTH,R.STARTDATE)) AS ENDDATE
[Stage].[RESULTS] AS R
[Stage].[RESULTSWO] AS RWO
ON RWO.SF_ID = R.SF_ID AND RWO.[ROW] = 1
R.[ROW] = 1
AND RWO.ENDDATE >= DATEADD(MM,@THISMONTH-1,R.STARTDATE)
@THISMONTH = @THISMONTH + 1
Please let me know what other information I can provide. Thanks.
P.S. We're on 2008 R2. (For now.)