To the OP - posting your data as attached spreadsheets is a less than ideal way of posting your problem. Please read Jeff's article on how to post...

Without sample data (CREATE TABLE and INSERT scripts), there's no way for us to test your query for you. So all we can give you is reasonably educated guesses. If...

Your parentheses look wrong.

SELECT x.*
FROM 
(     SELECT a
       FROM table1
       WHERE...
  UNION ALL
       SELECT b
 ...

Hard to argue with Jeff's advice.  Maybe spend a week on just text functions and do a whole lot of them. Start easy and then make them more complex as...

Use REPLACE()

Post the Oracle query then. Then modify if that doesn't work. There is a fair amount of overlap between Oracle and SQL Server dialects of SQL.  Try it and if...

This is homework. Give it a try and then tell us what you're having trouble with.

What happens if you use the 64-bit version? The problem with a 32-bit application is that it can only address about 3.5 GB or memory.

For someone who's been around as long as you have, you should know that's not a table.  If people can't recreate your scenario, you're not likely to get an answer. ...

What do you mean by "nearest to today"? Can be either before or after?  Then you'd have to do MIN of ABS(DATEDIFF....)  or return the top 1 value...

You shouldn't be grouping by EventDate - you're trying to return a MIN([EventDate]), so grouping by it (return each unique value) doesn't make sense.

If you have control over the design of the table, you could create a new column and extract just that part to the new column. Then you could index it...

Could you post some sample data - insert statements so we have something to work with? Basically, you can use LAG with a window to get the "previous" record, and...

Maybe this?
SELECT member,
    amt_paid,
    amt_adjusted,
    dt,
    dupeNum
FROM
( SELECT member,
    amt_paid,
    amt_adjusted,
    dt,
    ROW_NUMBER() OVER (PARTITION BY member, dt, amt_adjusted ORDER BY member, dt) AS dupeNum