Firstly apologies, Mohammad, for the obscenely long reply.

Before we look at the results, let me just say that the test data, and code that I used are those that I posted previously. So you are free to perform exactly the same test in your own environment.

I compared your method with the other methods previously discussed, and unfortunately the results were rather disappointing. Indeed it's generally the worst performer.

For the full resultset I used your code exactly as is. For the "Price rises" and the "One Item" queries, I amended your query to put the results in a further CTE and then filtered the results. (extract below)

C3 AS

(

SELECT MAX(CASE WHEN k = 0 THEN item END) AS Item,

MAX(CASE WHEN k = 1 THEN price END) AS OldPrice,

MAX(CASE WHEN k = 0 THEN price END) AS RangePrice,

MAX(CASE WHEN k = 0 THEN PriceStartDate END) AS StartDate,

MAX(CASE WHEN k = -1 THEN PriceStartDate END) AS EndDate

FROM C1

GROUP BY itemid, grp_fct

HAVING MIN(PriceStartDate) < MAX(PriceStartDate)

)

SELECT * FROM C3 where Item='Item 512'

Full ResultsetMethodExecution Time

CTE View13 secs

Temp Table with rownumber33 secs

Table Variable with rownumber47 secs

Temp Table with identity16 secs

Table Variable with identityCancelled after 1 hour

Cross Apply12 secs

Mohammad Salimabadi Solution131 secs

One Item

CTE View2 secs

Temp Table with rownumber5 secs

Table Variable with rownumber4 secs

Temp Table with identity6 secs

Table Variable with identity83 secs

Cross Apply0 sesc

Mohammad Salimabadi Solution85 secs

Price Rises

CTE View8 secs

Temp Table with rownumber19 secs

Table Variable with rownumber17 secs

Temp Table with identity12 secs

Table Variable with identityNot Run

Cross Apply8 secs

Mohammad Salimabadi Solution114 secs

Please try to reproduce this in your own environment, and let me know if you manage to bring improvements to the results.

Best regards,

David McKinney.