Thank you, this is very clear and exactly what i was looking for.

Thanks Scott,

That is certainly the result set that i am after. Is there are way to achieve this without using Tally's? i am a novice and struggle with understanding code...

No the source data is actually in financial periods that i split out into 2 columns. For example 1801, 1802, 1803, etc.

First 2 digits are the financial year and the...

Thank you

This is exactly what is was looking for.

Thanks Scott,

That was exactly what I was looking for.

I needed the week number per year (not a running tally of weeks) so the only change i made was  to change...

Hello Mike,

There is no sample data for this one. The table needs to be created dynamically and the image is supplied is ultimately an example of the result set i...

Thanks, this is perfect.

I was just looking at using a CTE when i saw your edit.

After some some time in google and advice from above i used the following to achieve this.

I used a 'table variable' to hold the list of tables and a while...

Perfect, thank you.

I need to run this against a fixed list of tables and UNION ALL the results. I am assuming that a cursor would be most appropriate but have...