Hi All,

I have looked at your solutions and tried them against my data and here are the results.

Bhuvnesh

Your code worked fine, quite fast - 11 Seconds, 267 rows

Table 'PowerUpdate'. Scan count 5, logical reads 88, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'PowerItem'. Scan count 5, logical reads 1820, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(141337 row(s) affected)

Table '#work_______________________________________________________________________________________________________________000000000170'. Scan count 4, logical reads 4904, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table '#work_______________________________________________________________________________________________________________000000000170'. Scan count 1, logical reads 1275, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(141337 row(s) affected)

(267 row(s) affected)

Table '#work_______________________________________________________________________________________________________________000000000170'. Scan count 5, logical reads 1274, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Warning: Null value is eliminated by an aggregate or other SET operation.

Chris Morris-439714 (1:52pm)

No end price in your query, 240 rows returned, results not accurate

(240 row(s) affected)

Table 'PowerUpdate'. Scan count 10, logical reads 176, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'PowerItem'. Scan count 10, logical reads 3640, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Here are the first two rows of the results

$49.95 Value Pack0.14112010-02-17 00:05:11.6232010-03-21 09:20:07.430

$49.95 Value Pack0.14912010-02-08 13:00:02.3572010-02-11 00:05:09.663

The First Row has the wrong End Date

Mark-101232

Your code is fast and apprears accurate, 268 row(s) in ~6 seconds

Table 'PowerUpdate'. Scan count 10, logical reads 176, physical reads 6, read-ahead reads 18, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'PowerItem'. Scan count 10, logical reads 3622, physical reads 24, read-ahead reads 1632, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Chris Morris-439714 : 4:06pm

Sorry but this did not work and failed with the following after 1 minute 20 seconds.

Msg 530, Level 16, State 1, Line 4

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Eugene Elutin

Your code had not completed after 14 minutes

For anyone who wants the real data that I was working with you can download the data from here.

http://blog.crowe.co.nz/attachments/powerdata.rar

I really appreciate the time you all have put into this query. I find it very cool to see the different solutions and hope one day I will be able to help others as you have done with me.

At this time Marks' code is fast and looks accurate, thanks very much.

Cheers

Chris