Remember, your CTE is going to go through the entire table to get the row numbers and then again to get them for the previous ID and then again to...

my query would just show you the next closest value, so you should be all set.

I'm not sure I follow what you are trying to achieve, but this might be what you are looking for, however it will likely be expensive to run:

select id, sub,...

There are likely other ways to achieve this, but you can do the following:

Select * from TableName T

where T.Date = Convert(date,getdate())

and T.Time = CONVERT(time,getdate())

You get records where the date and...

it does mean dynamic SQL. It can be in a stored procedure or the client, but since you searched the database I would say it is definitely the client.

you should have an RPC:Completed event (assuming you are tracing it) after your event in question with the same SPID. That is the calling procedure.

i defer and agree with gsquared.

You will need to pass the output to a table variable. Otherwise the procedure is going to return the contents of the Output operation.

then you can do something like:

if...

you need to do the following structure:

DECLARE @TableVariable Table

INSERT INTO

(Field list)

OUTPUT INTO @TableVariable...

SELECT Statement

Then you have the @TableVariable that would contain what you inserted.

Lookup INSERT and OUTPUT in Books...

couple of items:

1) The insert statement is incomplete....what are you trying to insert?

2) What is IF output inserted.ITEMNMBR ='NULL' ??

3) Is the field ITEMNMBR a null or...

Excellent idea....and safer. 🙂

be careful how you remove your PK. If you script it with a drop, dropping a clustered index will delete the table.

what are the steps of the job? Can you post the SQL being executed?

Task Manager's I/O number could be misleading without knowing how long the server has been running. What are the specs of SQL Server? Min/Max memory settings. What...

my original SQL should work for you then.

select Investigation_Id, Case_Id, Investigation_Note, Max(DateTime) MaxDate, Team_Id, Status_Id

from tablename

group by Investigation_Id, Case_Id, Investigation_Note, Team_Id, Status_Id