One way to compare a given role with another one is to use EXCEPT.   The trouble is that you have to do it both ways, meaning SELECT * FROM ROLE1_DATA...

Note that I added the having clause to the posted query above, rather than repost the whole shootin' match.

Not sure if this will perform better, but given the number of CASE statements, it may.   Also, do you still have a need for a HAVING clause?
WITH TABLEA AS...

There are a couple of references in your query to dbo.TABLEW, but your second CTE is TABLEW.   No amount of adding a HAVING clause is going to make the query...

Given your requirements, you appear to only need to worry about holidays that occur within the first 3 calendar days of the month.  US only has Independence Day (July 4th,...

I'm pretty sure you can't run SSIS in 32-bit mode AND go after an ODBC source via the 64-bit driver.  If your ODBC Connection uses the 64-bit driver, that's quite...