In the data here, SAMPLE_ID_ALT needs to be updated to what I have typed in. For every 'NS' in the SAMPLE_ID column, the SAMPLE_ID_ALT needs to be set incrementally to NS_001, NS_002 and start again when a new SITE_ID is found.
I have tried this, and can see why the optimizer might not like it, but I cannot find a workaround. Can anyone help please? I would rather not use cursors if I can help it.
SET SAMPLE_ID_ALT = 'NS_'+ RIGHT('000'+CAST(ROW_NUMBE
OVER (PARTITION BY SITE_ID
ORDER BY DEPTH_FROM)
WHERE SAMPLE_ID = 'NS'