Will the result set always be for one individual or Invoice? If so, provided that your indexes are in the right place, it not going to make a huge difference,...

What version of SSIS are you using? If 2012+ use an expression task to set your varible value to the expression you need. Something like:
[@User::FileName] = {FileName}_{Your Date Expression}.xlsx

This?
SELECT C.cost_value + C.tax_value AS Payment_value
FROM #costs C
WHERE C.payment_method = 'Direct'
UNION ALL
SELECT SUM(CASE C.payment_method WHEN 'Credit Card' THEN C.cost_value + C.tax_value
   ...

Reformatted SQl for my own sanity and readiblity:
select -- "String name" = is deprecated, I suggest using AS
Case WHEN adm_ts >= @PriorFOM AND dschrg_ts <=@PriorEOM--admit greater...

Been a very long time since I did Java (I was 18 and at university!), so afraid I've no on this one. sorry.

Does your state run OK if you run the SQL on it's own and not inside the application? I'm not sure that's a SQL error, but rather a programming error....

These are beginning to look like we're just doing your job for you. This is the third post you've made with very similar requirements.

What have you tried so...

Always check your SQL works before posting 😉 You don't declare a schema before the name of a temporary table. I was also getting conversion errors on your datetimes:

Can you provide consumable sample data that shows the problem? We can't see access data, so we don't know what we should be expecting from your SQL.

Because of the wording the OP used, I want to double check; You say that the data set brings back data over a period of time. In your example you...

Could you supply consumable data please? 🙂

Personally, I'd just keep all the historical currency data. Unless storage space is really than much of a premium for you, it's not going to be a problem, as storage...