Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.
DECLARE @MAXMONTHS INT SELECT @MAXMONTHS = MAX(DATEDIFF(MM,STARTDATE,ENDDATE)) + 1 FROM [Stage].[RESULTS] -- ADD 1 IN ORDER TO ACCOUNT FOR ROUNDING DOWN IN YEAR CALCULATION DECLARE @THISMONTH INT SET @THISMONTH = 1 WHILE (@THISMONTH <= @MAXMONTHS) BEGIN INSERT INTO [Stage].[TPHASES] SELECT R.SF_ID , R.AP_ID , @THISMONTH AS Phase , DATEADD(MM,@THISMONTH-1,R.STARTDATE) AS STARTDATE , DATEADD(D,-1,DATEADD(MM,@THISMONTH,R.STARTDATE)) AS ENDDATE FROM [Stage].[RESULTS] AS R INNER JOIN [Stage].[RESULTSWO] AS RWO ON RWO.SF_ID = R.SF_ID AND RWO.[ROW] = 1 WHERE R.[ROW] = 1 AND RWO.ENDDATE >= DATEADD(MM,@THISMONTH-1,R.STARTDATE) ORDER BY R.SF_ID SET @THISMONTH = @THISMONTH + 1 END ;