UPDATE statement not consistent when including NULL fields
In SQL Server, when you concatenate string, if one part is NULL the result of the string will be NULL. To avoid this behavior, you have to test all your string with the ISNULL function to ensure a string empty instead of the NULL value.

UPDATE Notes

SET

/* append previous month to notes */
Notes=ISNULL(Notes,'') + CHAR(13)+CHAR(10) + CHAR(13)+CHAR(10) + '---' + CAST(GETDATE() as         nvarchar(max)) + CHAR(13)+CHAR(10) + cast(ISNULL(oppo_rota_last_progress_update,'') as nvarchar(max))

/* replace previous month with current month */
, LastMonth=CurrentNote

/* replace previous month with current month*/
, CurrentNote=cast('To be updated' as nvarchar(max))