Calculating the maximum seen so far for each point in time

What I'm trying to calculate is a bit tricky to describe, so please bear with me...

I have a table with product numbers, and the price of each product each week over a period of 4 years. Price can go up or down between the weeks. For each date, I'm trying to calculate the maximum price up to that point in time for each product.

In the sample table below, the third column (Price) is the data I have; the fourth column (Maximum Price PIT) is what I'm trying to calculate. - EDIT: to be clear, this column does not exist yet!

For example, on 15/01/2012 for product 001, the maximum price seen up to that point in time was 50, even though in the next week it goes up to 60 (which is the maximum over the whole period).

I think that I might need to use some kind of loop, but I really don't know where to start. I have a pretty good grip of the basics of SQL, but I get a bit fuzzy when it gets to window functions and further. I've googled the problem quite a bit, but haven't been able to find any pointers.

Can anyone help? Let me know if you need me to clarify what I'm asking for!

Product | Date | Price | Max Price PIT (this column is what I want to calculate)
001 01/01/2012 25 25
001 08/01/2012 50 50
001 15/01/2012 35 50
001 22/01/2012 60 60
001 29/01/2012 50 60
001 05/02/2012 15 60
002 01/01/2012 18 18
002 08/01/2012 7 18
002 15/01/2012 10 18
002 22/01/2012 20 20
002 29/01/2012 30 30
002 05/02/2012 25 30

Thank you!

p.s. the table is 19391864 rows.

If you need a rolling max you can use both CROSS APPLY or LEFT JOIN, here is the CROSS APPLY version

SELECT Product, Date, Price, b.[Max Price PIT]
FROM   Table1 a
       CROSS APPLY (SELECT MAX(Price) [Max Price PIT]
                    FROM   Table1 b
                    WHERE  a.Product = b.Product
                      AND  a.Date >= b.Date) b

SQLFiddle demo

With SQLServer 2012 or better it'll be possible to use the new ROWS clause to get the same effect

SELECT Product, Date, Price
     , MAX(Price) OVER (PARTITION BY Product ORDER BY [Date] 
                        ROWS UNBOUNDED PRECEDING) [Max Price PIT]
FROM   Table1

The key point is to get all the rows before the current for the group and apply the statistical function only to them, sometime this type of presentation is called "rolling", for example rolling sum/total if you get the total value of cash till the current row.

StackExchange Database Administrators Q#67881, answer score: 7