this will give you the months

SELECT

LEFT(CONVERT(VARCHAR,DATEADD(m,-n+1,GETDATE()),0),3) + ' ' + CAST(YEAR(DATEADD(m,-n+1,GETDATE()))AS VARCHAR(4))

FROM Tally

WHERE N < 14

And you can use the same type of query to work out the ranges

If...

OK cool thanks

Could you supply the indexes on these three tables please:

JobDetails

JobMeasures

Organisation

Also here is another way to do the split function without using a loop it requires a tally table...

Could you not try and chagne the functions to be inline functions?

Secondly, could you save the graphical execution plan from SSMS and attach that file, as it's easier to read.

thanks

Chris

Yip I agree, Multi-line functions in where clause are not good for performance at all as they tend to run for every row.

as mentioned the Converting of dates on your...

oops sorry no you didn't say that.

It seems to be the little person on my shoulder that keeps nagging me about it

Hi Gail,

any advice on why the Deferred naming option is a NO NO 🙂

OK I've tested this on a table of 100000 rows using the first solution with a GO and then my solution

The performance seems to be pretty much the same with...

oh.

Well will get that error because there is no deferred naming cause SQL knows which table you using and notices that the column is not there, if your select included...

Why would you do a select * in your code anyway?

OK I think I have a solution but it's messy.

But it definitely works from what I can see (but I prob wouldn't use it myself) would be interested to see...

The answer is yes

CREATE TABLE tmp

(col1 INT IDENTITY,Col2 INT)

INSERT INTO tmp VALUES(1)

SELECT * FROM tmp

INSERT INTO tmp VALUES(1)

DBCC CHECKIDENT(tmp,reseed,19)

SELECT * FROM tmp

the question is why do you want to do...

I'm not 100% sure what you asking for .

The statement you supplied will not run the update? is that what you expect?

mmm that sounds AWESOME he he he

Well once again, thanks for help Gail 🙂

I hope the OP finds this as useful 🙂

Thanks Gail,

Would there be a big difference between using the num_ordem in the Key as to having it in an Includes?

normally without a aggregate I think an includes would...

Also it seems the sort is most costly thing in your query so, I guess I need to ask why you have the current clustered index set up?

Could you clustered...