Thank you for all of the input! This has given me a great start.

Just this piece works:

SUBSTRING(FILE_NAME, PATINDEX('%[_]20[0-9][0-9][0-1][0-9][0-3][0-9][_]%', FILE_NAME) + 1, 8)

Thanks for the help!

its grabbing the c.CYCLE_DT from the CYCLE table. If I add an ACCT_ID in the WHERE clause of the query it will return the result expected.