I have a cursor that I am trying to replace (perhaps unnecessarily) in an attempt to clean up a stored procedure. Essentially what it is doing is counting each note for each member in a temp table and then assigning each of those notes a sequential value into the field
NOTE_COUNTER (int) of the temp table. Here is the original code for it with the variables:
@lUPI_current CHAR(32), @lCASE_SEQ_current INT, @lDIFF_ENCOUNTER_DATE INT, @lcounter INT SET @lUPI_current = '' SET @lCASE_SEQ_current = '' SET @lDIFF_ENCOUNTER_DATE = 0 SET @lcounter = 0 DECLARE CURSOR_NOTE_COUNTER CURSOR FOR SELECT MEMBER, CASE_SEQ, ENCOUNTER_DATE FROM #TMP_NOTES WHERE TOTAL_CALLS > 1 ORDER BY MEMBER, CASE_SEQ, ENCOUNTER_DATE OPEN CURSOR_NOTE_COUNTER FETCH NEXT FROM CURSOR_NOTE_COUNTER INTO @lUPI_current, @lCASE_SEQ_current, @lDIFF_ENCOUNTER_DATE WHILE (@@FETCH_STATUS = 0) BEGIN SET @lcounter = ( SELECT MAX(NOTE_COUNTER) FROM #TMP_NOTES WHERE MEMBER_UPI = @lUPI_current AND CASE_SEQ = @lCASE_SEQ_current ) UPDATE #TMP_NOTES SET NOTE_COUNTER = @lcounter + 1 FROM #TMP_NOTES WHERE MEMBER_UPI = @lUPI_current AND CASE_SEQ = @lCASE_SEQ_current AND DIFF_ENCOUNTER_DATE = @lDIFF_ENCOUNTER_DATE FETCH NEXT FROM CURSOR_NOTE_COUNTER INTO @lUPI_current, @lCASE_SEQ_current, @lDIFF_ENCOUNTER_DATE END
What I Have Tried
I tried replacing this with a CTE because of the notion that cursors should be avoided in favor of working on sets of data. Here was my attempt at that, the stumbling block being the ambiguity of the column names since it is working on the same table.
WITH RecordsBeingUpdated AS ( SELECT MEMBER_UPI, CASE_SEQ, DIFF_ENCOUNTER_DATE, ROW_NUMBER() OVER (PARTITION BY MEMBER_UPI, CASE_SEQ, DIFF_ENCOUNTER_DATE ORDER BY DIFF_ENCOUNTER_DATE ASC) AS RowNum FROM #TMP_NOTES WHERE TOTAL_CALLS > 1 ) UPDATE #TMP_NOTES SET NOTE_COUNTER = R.RowNum FROM RecordsBeingUpdated AS R WHERE MEMBER_UPI = R.MEMBER_UPI AND CASE_SEQ = R.CASE_SEQ AND DIFF_ENCOUNTER_DATE = R.DIFF_ENCOUNTER_DATE
Please tell me if there is a cleaner way to doing this. I am grateful for any time that the community can provide.