Merge with a CTE in SQL Server

Question

I have using this merge statement, but it is not updating my target table. It is not throwing any errors and is returning the results of the cte, greater. I would expect it to update rows that need to be updated and insert rows that need to be inserted and return how many rows were affected.

with greater (provider) as (
  select 
    provider 
  from 
    (
      select 
        h.provider, 
        npi 
      from 
        hha h 
        join Quality q on h.provider = q.provider 
      group by 
        h.provider, 
        npi
    ) a 
  group by 
    provider 
  having 
    count(*) > 1
), 
zip_npi (providernbr, zip, npi) as (
  select 
    providernbr, 
    zip, 
    npi 
  from 
    npi 
  group by 
    providernbr, 
    zip, 
    npi
), 
zip_quality (provider, zip, rating) as (
  select 
    provider, 
    zip, 
    rating 
  from 
    Quality
) 
select 
  a.provider, 
  c.npi, 
  'HHA', 
  max(rating) as rating 
from 
  greater a 
  join zip_quality q on a.provider = q.provider 
  join Zip_npi c on q.provider = c.providernbr 
  and q.zip = c.zip 
where 
  rating <> '' 
group by 
  a.provider, 
  c.npi merge provider as [Target] using greater as source on (
    [Target].npi = [Source].npi 
    and [Target].provider = [Source].provider
  ) WHEN MATCHED THEN 
UPDATE 
SET 
  [Target].rating = [Source].rating WHEN NOT MATCHED THEN INSERT (provider, npi, type, rating) 
VALUES 
  (
    [Source].provider, [Source].npi, 
    'HHA', [Source].rating
  );

Answer 1

Your last SELECT (after zip_quality) should also be part of the CTE, so something like this:

... ), 
zip_quality (provider, zip, rating) as (
  select 
    provider, 
    zip, 
    rating 
  from 
    Quality
), something_else (provider ,npi, some_text ,rating ) as (
select 
  a.provider, 
  c.npi, 
  'HHA', 
  max(rating) as rating 
from 
  greater a 
  join zip_quality q on a.provider = q.provider 
  join Zip_npi c on q.provider = c.providernbr 
  and q.zip = c.zip 
where 
  rating <> '' 
group by 
  a.provider, 
  c.npi 
)

merge into provider as [Target] 
using something_else as source 
on (
    [Target].npi = [Source].npi 
    and [Target].provider = [Source].provider
  ) WHEN MATCHED THEN 

UPDATE 
SET 
  [Target].rating = [Source].rating WHEN NOT MATCHED THEN INSERT (provider, npi, type, rating) 
VALUES 
  (
    [Source].provider, [Source].npi, 
    'HHA', [Source].rating
  );