Or you could use Jeff's favourite method: 😛

declare @trend table (keyinstn INT, ratingdate datetime, rating varchar(10))

declare myCursor cursor for
select distinct t.keyinstn,convert(date,ratingdate) ratingdate1
 ...

Or using an inline table:
select c.keyinstn, c.ratingdate, c.rating
from (select t.keyinstn,ratingdate, rating,
               ROW_NUMBER() OVER (PARTITION BY convert(date,ratingdate) ORDER...

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