Hannover Fist Hannover Fist - 4 months ago 10
SQL Question

SQL Update with Multiple updates per Record

I am trying to update months of a given persons records to reduce their potential if they get a claim within a month.

UPDATE M
SET POTENTIAL_HITS = POTENTIAL_HITS - 1
FROM #TEMP_CDC_MEMBERS M
INNER JOIN #TEMP_CDC_CLAIMS C ON M.CIN = C.CIN AND C.MEASURE_INDICATOR = M.SUB_MEASURE
WHERE M.MOE > C.MOE


The MOE is a month field in a 201607 format.

The problem I am having is if there is a hit in one month, that difference will be ignored in a later month (due to SQL transactions).

Is there a way to update the potential without using a loop?

Answer

Your problem is that update doesn't do cumulative updates -- only one update per record in m. I think you can do what you want using cross apply:

UPDATE M 
SET POTENTIAL_HITS = POTENTIAL_HITS - c.cnt
FROM #TEMP_CDC_MEMBERS M CROSS APPLY
     (SELECT COUNT(*) as cnt
      FROM #TEMP_CDC_CLAIMS C 
      WHERE M.CIN = C.CIN AND C.MEASURE_INDICATOR = M.SUB_MEASURE AND M.MOE > C.MOE) c;