Intro: I have to admit to belong to the huge group of developers who do not use WINDOW functions on a daily basis – cf. Glenn's blog's article on OLAP...

Today, I have to build a quite complex calculation:

Say, you have a table of the history of some entities, and you have to find out which entities have had particular steps in their history. Less abstract: Take the SQL Anywhere sample database, and you would have to find out the products whose quantity has decreased a particular number on a single day.

Imagine there would be an additional table ProductsHistory which contains the quantity of each product at the end of any day (something like a daily inventory), something like

create table ProductsHistory (
   ProductID int not null,
   ProductDay date not null,
   Quantity double not null,
   primary key (ProductID, ProductDay),
   foreign key (ProductID) references Products(ID)
)

As an example, the table might contain the following quantities for product 400 (the Cotton Baseball Cap) for the last 10 days:

insert ProductsHistory values (400, dateadd(dd, -9, current date),  90);
insert ProductsHistory values (400, dateadd(dd, -8, current date), 130);
insert ProductsHistory values (400, dateadd(dd, -7, current date), 108);
insert ProductsHistory values (400, dateadd(dd, -6, current date), 107);
insert ProductsHistory values (400, dateadd(dd, -5, current date), 101);
insert ProductsHistory values (400, dateadd(dd, -4, current date),  88);
insert ProductsHistory values (400, dateadd(dd, -3, current date),  53);
insert ProductsHistory values (400, dateadd(dd, -2, current date), 121);
insert ProductsHistory values (400, dateadd(dd, -1, current date), 117);
insert ProductsHistory values (400, dateadd(dd, -0, current date), 112);

How would I find out those products (and the according day) whose quantity has decreased by 20 pieces or more on any day? - That should return the 3rd and 7th row for this product.

Using min/max and comparing them won't do as each entry has to be compared with its ancestor. That seems fitting for a WINDOW expression. However, according to the docs, WINDOW expressions can only be used with aggregate functions or statistical functions, and here a simple substraction would do: quantity from day x - quantity from the day before.

So what can be done?

(The answer is about to come...)