I realise that I've not posted the table definitions, but if anyone can give any insight here I'd really appreciate it: https://www.sqlservercentral.com/Forums/1897549/Execution-time-Improvement-Knowing-the-problem-but-not-the-solution

I'm pretty confident the problem...

You need to likely use a CROSS APPLY or something similar. With no sample or expected data I've no idea what your data looks like, but here's an example of...

If you are using the EXACT same SQL that is being run by the application (captured in profiler) under the same credentials, and SSMS is returning results and your application...

This is a bit of a guess, based on your small dataset, but this, at least, might get the ball rolling:
SELECT *,
       CASE WHEN...

This might seem a silly thing to say, but what have you done to check yourself? The best way to see which query performs better is to look at the...

What do you mean by the results are not the same? Could you be more specific? Provide sample outputs for one and the other and why they differ?