Dwain, good thinking there.

SELECT

PersonId,

GoalDate,

GoalStatus,

SUM(1-GoalStatus) OVER(

PARTITION BY PersonId

...

The idea is similar but I hanged it a little bit to get the custom enumeration.

Calculate the running total of GoalStatus = 0 by PersonId ordered by GoalDate. Calculate the...

It will be helpful if you can include more sample data.

Since you are using SS 2012, one idea could be to calculate the running total of GoalStatus = 0 by...

I would suggest to read the predicate expressions one more time.

...

Where dt is not NUL and dt <> '19000101';

can be simplified as:

...

Where dt <> '19000101';

As you can see from your...

Try:

SELECT

MATURITY_ID AS [IDENTIFIERS/IDENTIFIER],

(

SELECT

INSTRUMENT_ID AS [@type],

val AS [@val]

FROM

#ElementContents AS E

WHERE

E.MATURITY_ID = T.MATURITY_ID

FOR XML PATH('RATING'), TYPE

) AS [RATINGS]

FROM

(SELECT DISTINCT MATURITY_ID FROM #ElementContents) AS T

FOR XML PATH('ASSET');

GO

There is no harm by having both expressions but having "LastDate <> '19000101'" is enough to cover both cases "is not NULL and is diff from '19000101'".

WHERE ...

...

If there is any improvement in my suggestion for sure it is not because I used PIVOT operator. The same pivoting was done in Lynn's suggestion (grouping, spreading and aggregating)....

> One, using a table variable isn't necessarily a good choice for testing. No matter how many rows of data exist, the optimizer will assume 1 row as there are...

The key here will be having an index to support the ranking function and avoid a "sort" iterator in the execution plan.

I would filter first, then aggregate, then enumerate the...

For version 2008 or greater check the following catalog views in BOL:

- sys.sql_expression_dependencies

- sys.dm_sql_referencing_entities

- sys.dm_sql_referenced_entities

The List of columns in the IN subclause should be inside parenthesies.

... IN (' + @columns + N') ...

Can you post the whole script?

I wonder if you declared @sSql as NVARCHAR(MAX) or a length big enough to hold the whole query.

I agree with Lutz. You can name the columns as ([4], [3], [2], [1], [0]) and use that number together with the report date to figure out the date value...

Because the requirement is to have an xml file, not a caracter text one, with the prolog / encoding / descriptor or whatever it is. SQL Server does not allow...

Have the descriptor in a separated file and do the concatenation at the command side using COPY.

c:\>copy f1 /B + f2 /B f3 /B