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.

• Filter the data so that the date is less or equal your point in time then find the maximum, or you need a different result? Commented Jun 9, 2014 at 16:04
• Hi Serpiton, I want to do it automatically to calculate the entire "Max Price PIT" column for every product, not filter each date by hand. If there is a way of creating a loop to do this it would be great. I need the entire time series. Commented Jun 9, 2014 at 16:08

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.

• Oh wow that makes so much sense! I didn't realise it could be so simple... I'll try it out and report back if it works. Commented Jun 9, 2014 at 16:31
• It seems to be working - thank you so much Serpiton! Commented Jun 9, 2014 at 17:16

I think sqlserver 2012 supports:

``````select Product, Date, Price
, max(Price) over (partition by product
order by date
rows between unbounded PRECEDING
AND CURRENT ROW
) as max_price
from T;
``````

but that sqlserver 2008 does not. If that is the case something like:

``````    select Product, Date, Price
, ( select max(Price) from T T2
where T2.product = T1.product
and T2.date <= T1.date ) as max_price
from T T1;
``````

will work, but not as efficient as the first one

One way is to do the loop in a recursive CTE where you fetch the first values per product in the anchor part and pick the next row with a higher price for each turn in the recursive part and store the result in a temp table.

The temp table will then hold the rows where a product changes the price to a higher value.

Use the temp table to pick the highest price so far for each row in your main query.

The table definition I used in the query:

``````create table T
(
Product int not null,
Date date not null,
Price int not null,
primary key (Product, Date)
);
``````

Some code to fill the table with data to test on. 100000 products over 200 week

``````insert into T
select top(20000000)
(row_number() over(order by (select null)) -1) / 200,
dateadd(week, (row_number() over(order by (select null)) -1) % 200, '2014-01-01'),
abs(checksum(newid()))
from sys.all_objects as o1, sys.all_objects as o2, sys.all_objects as o3
``````

Query:

``````with C as
(
select T1.Product,
T1.Date,
T1.Price
from T as T1
inner join (
select T.Product,
min(T.Date) as Date
from T
group by T.Product
) as T2
on T1.Product = T2.Product and
T1.Date = T2.Date
union all
select T.Product,
T.Date,
T.Price
from C
cross apply (
select row_number() over(partition by T.Product order by T.Date) as rn,
T.Product,
T.Date,
T.Price
from T
where C.Product = T.Product and
C.Date < T.Date and
C.Price < T.Price
) as T
where T.rn = 1
)
select C.Product,
C.Date,
C.Price
into #T
from C
option (maxrecursion 0);

create index IX_#T on #T(Product, Date) include (Price) ;

select T.Product,
T.Date,
T.Price,
TM.MaxPrice
from T
cross apply (
select top(1) TT.Price as MaxPrice
from #T as TT
where TT.Product = T.Product and
TT.Date <= T.Date
order by TT.Date desc
) as TM;

drop table #T;
``````

If this query will be faster or slower than the answer provided by Serpiton depends on how price has evolved over time. If price is ever increasing for each week then this answer will be slower.