try

avg(SUM(CASE WHEN Value=55 THEN (Perf_YTD - BB_Return_YTD) ELSE 0 end))?

Also, for "grand total"s, try GROUP BY ... WITH ROLLUP

insert can just be prepended to a select. You need to make sure you're getting the right data.

select [Consultant],

sum(case when [Model]=55 then 1 else 0 end) "Model 55",

sum(case when...

.note{font-style: italic;font-family: serif !important;color: red;Jeff,

I haven't forgotten them, but I thought this would yield more performance.

Lynn,

Obviously, I should have approached this with a bit more background. My ultimate goal...

Right, but I'm always looking for better ways to do things.

This is what I'm using to generate an initial working set:

SELECT a.[id1],a.[id2],a.,a.[assignDate]
FROM [sampleTbl] a
JOIN 
(/*fetches all the previous open records...

What does the "a.RowNumber = (b.RowNumber - 1)" do? it doesn't look at the previous row, does it? If so, that would be bad, because you don't know...

Lynn, I would, except the CTE is 18 lines, and the principle use is 30 lines. The basis is a historical table, like this:

id-aid-buserdate
16251413761 2005-03-26 00:00:00
16251416241 2006-12-07 00:00:00
16601306739 2005-11-07...

Yep, CTE's seem to do the job. I just wish the rest of it was smoother.

Interestingly, I couldn't run two consecutive selects off of it; I probably don't understand...

Yes, I was aware of that. However, what I'm trying to do is abstract the proc away from the schema as well as make it more independent. In...

because I want to write a stored proc to do inserts. It means that I have a hard-coded insert statement; that is, the columns are already named and in...

I see. So is this just Microsoft being dumb, or is it really a SQL standard?

I can't even do this:

INSERT @example ([preference])

VALUES (CASE WHEN @test-2 IS NULL THEN DEFAULT ELSE @test-2 END)

The idea, I thought, was that 'default' will insert whatever the default value of the column is. You're saying I have to hard-code a query for every column? ...

Wait, I think I found it. [sd_spvsrrev] IS NULL in the top, [sd_spvsrsub] in the bottom.

Thanks for the reply.

Forgive my naiveté, but I'm not really sure what an "indexed view" is. Is that a view that puts substring(target,1,9) into one column, and substring(target,11,4) and...

You can use a stored proc to do it:

sp_rename [ @objname = ] 'object_name' ,

[ @newname = ] 'new_name'

[ ,...