Here's another way to do it:
;with cte as
(
    select t.keyinstn,ratingdate, rating,
           ROW_NUMBER() OVER (PARTITION BY convert(date,ratingdate) ORDER BY...

Here's one way to do it:

;with cte as
(
    select distinct t.keyinstn,convert(date,ratingdate) ratingdate1
      from trend t
)
select c.keyinstn,...

It's not going to make much difference to the performance but and SS.QTY_RECEIVED is not null is not required because
and SS.QTY_RECEIVED <> 0 will...

from qnxt.dbo.memberpcp mp
join qnxt.dbo.affiliation a1 (nolock) on mp.affiliationid = a1.affiliationid
join qnxt.dbo.affiliation a2 (nolock) on mp.paytoaffilid = a2.affiliationid
join qnxt.dbo.provider p1 (nolock) on a1.provid = p1.provid
join...

Have you tried FULL JOIN instead of INNER?

All our scripts have a CREATE that's executed if the sp doesn't exist followed by an ALTER. That way all the security permissions are preserved. We also sometimes have comments...

SSIS can create its own system logging table
select * from dbo.sysssislog

Another reason why you should always prefix the column names with the table name (or alias) .

You will get better performance from an UPDATE followed by an INSERT than you'll get from a MERGE.

Your primary key constraint name is the same as your table name.
Change it to: CONSTRAINT PK_CSize PRIMARY KEY (CID),