After re-reading the code you provided I agree that my recommendation regarding PortfolioCode/PortfolioID doesn't make that much sense. I totally overlooked that you need it for the insert and you...

Option 1:

SUM(CASE WHEN A='X' THEN 1 ELSE 0 END + CASE WHEN...END)

Option 2: use UNPIVOT and the statement above on the resulting column.

Sorry for being imprecise... 😉

Right at the beginning of your *cough* c.u.r.s.o.r. you populate the WannaBeTempTable @DelPortList

INSERT INTO @DelPortList

SELECT DISTINCT psa_s.PortfolioID, psa_s.AsOfDate

FROMPortSecAgg_staging AS psa_s

WHEREpsa_s.BatchGroup = @BatchGroup

I'd expand this table...

Craig Farrell (12/8/2010)

---

LutzM (12/8/2010)

---

How do you populate the PortSecAgg_staging table?

Do you really need to update portfolioID and securityID for each and every row?

That basically means to join 36mill rows to...

How do you populate the PortSecAgg_staging table?

Do you really need to update portfolioID and securityID for each and every row?

That basically means to join 36mill rows to 48mill rows without...

Hmmm....

Given the solutions already provided I'd recommend you study how both versions work.

I'm sure you'll find the solution by yourself.

Strong hint: Have a look at the WHERE clause as well...

Did you realize that your requirements did change between the original and your latest post?

TERM was unique per ID and there were no duplicate CLASS values per ID either nor...

What have you tried so far and where do you get stuck?

Try using the ROLLUP operator.

Something like

SELECT

level1,

level2,

level3,

SUM(CalcField1) ,

GROUPING(level1) AS 'IsGrandTotal',

GROUPING(level2) AS 'IsTotal_Level1',

GROUPING(level3) AS 'IsTotal_Level2'

FROM yourTable

GROUP BY level1,level2,level3

WITH ROLLUP

For details please see...

Hey Steve, what size of a steak you were able to hide underneath the green stuff? :-D:hehe:

Craig Farrell (12/7/2010)

---

...

One of the nice side effects of constantly being a joker is you can get away with a few comments others would be slammed for.

Nope, I never...

I'm expecting some non-numeric values in [QR_FreeText] for [BSU_MarketSector]='M'.

It seems like query optimizer decides to filter on [BSU_MarketSector] [QR_FreeText] first, therewith ignoring the invalid column values of [QR_FreeText].

The article Chris...

That's a rather vague description...

Please post table def and sample data as described in the first link in my signature together with the expected result.

touchmeknot (12/6/2010)

---

Thanks for the reply.

The table definition is big and thus I did not post it here.

The column1 is varchar(100) NULL.

I meant that the second query results in records where...

The two queries are different.

The first one would not return a row with column1='abcd', the 2nd one would.

It might be possible there are other character than a space.

Can you post...