Thank you - I thought there had to be a way but couldn't quite get there and wasn't given the time to work it out.
Checking the execution plan I see why you have concerns about it being resource intensive. There are certainly a lot of table scans going on! Works nicely though and well thought out.
I wrote a stored procedure after I posted using a couple of temp tables and single row processing. It's not Set based, but it does work and while there are a lot of queries involved they are all Clustered Index Scans for the most part returning a single row. Might be more maintainable for my replacement if I get hit by the proverbial I.T. bus.
My actual data requirements are for the last 5 years of data per staff member, but thankfully this is a small company so really only talking between 50-60 staff members to report on so it's not like it's a huge server hit. I will give both methods a shot and the one which crosses the finish line first will get the job.
Thank you for taking the time with this it is very much appreciated.
Mike