Certainly Its a very very complicated select statment,..

Wondering if it is possible to avoid the outer join with cicmpy enduser after the outerjoin between renewlletter and the cicmpy subscriber...it gets to complicated.

Also, i bet you have check the result of your select stattement.. can you check it again and make sure that the select statement is giving you more than one null in result. As more than one Null would violate the unique constraint.

Try doing this in your test server and see if it works

CREATE UNIQUE INDEX accountcode ON cicmpy (subscriber)
    WITH (IGNORE_DUP_KEY = ON);

PS: Remember its Monday, stressful day for most of us.