Why do you have the where clause?:

where n.type = 'p'

By the way, the maximum count I need is:

18711

So I will need to construct my own table...

John,

I have just tested your assumption and I don't think that you are correct.  Unless I have made another mistake in my changes of code.

It is this bit that I have a problem with:

ISNULL(@variable, 'anything other than x') <> 'x'

No it is just a simple insert statement. 

Interesting looking code.  I'm not sure if I fully understand the logic of it.  In fact, I don't believe that it will work.

Thanks John.  I'll take your word for it.

No.  I mean where there is no max value because there are no records in the table.  When this is true I want it to return the value 1 and...

You might be on the right lines but it does not look right to me.  Can you post a detailed explanation of the code please.

I have Visual Studio.  I don't think that I have VSS.  What is that anyway?  It is only worth implementing a solution to this if it is quick and easy. ...

I’ve thought of some more:

The quicker you learn to treat your brain as the most precious piece of hardware available...

I think that we are going down the wrong path with this.  I have decided to create a tempory lookup table for the solution.  But your posts have been inteteresting...

I am sure that this solution is on the right lines.  How do I set the start and end date for the financial year?  Also, can I create a function...

I wish that SQL had a function that would allow me to do something like:

SELECT "Financial Year Number" = FINANCIALYEAR('03/12/1998')GO

The financial year in the uk runs from the 1st of April to the 31st of March. 

Nothing sophisticated.  When I set myself tasks I paste in the SQL solution to the task sheet and then move it to a specified folder that holds SQL solutions (within...