For clarity, we show each fiscal week associated with its end-date. To do this
we probably need to join to a Dimension table to translate the FWK_CD to an end-date. Those who use actual dates for time
dimension keys would not need this extra join, as the end date would be on the ACTUALS fact. The last column shows the actual
enumeration we are seeking to compute. Weeks 2006W19 through 2006W23 all have sales, so they are sequentially enumerated as
shown in the last column. Week 2006W24 has no sales, and does not appear in our table. Week 2006W25 is the next week that
does, and has to start with sequence number 1 again. Three more consecutive weeks appear, and then there is another gap.
The resulting enumeration can be stored or always derived on the fly to answer
questions such as “Find stores that sold SKU 123 for N consecutive weeks during the last year.” The same concept
is often used to enumerate weeks out-of-stock. Instead of our table showing weeks that had sales, it shows weeks that the
store was out of stock. Various supply-chain or financial methods use such metrics. Here we will only concern ourselves with
the technique of enumeration rather than its purpose.
The question is how do we write a SQL query that produces these sequence numbers?
A SQL Solution
One approach is to use a recursive method of traversing consecutive chains
of sku/locations over time, and applying the enumeration. This is possible through a procedure, but can also be done with
a recursive union, which is now supported by most major database platforms. The problem is that either of these approaches
is expensive in resources and very slow. Recursion requires indexed direct access, which is far slower than stream operations
that can be pipelined and more effectively parallelized by the DBMS.
Our approach uses the OLAP extension “rank.” We can assume that
our set of sales data can be partitioned into many little sets, one for each SKU/STORE. Within each such partition, we will
create a rank ordering all rows by time. The rank is a contiguous sequence number for each period that appears in the SKU/STORE
group regardless of time gaps.
Another useful number we can assign to each row is a sequential number of the
week referenced by the row. We can use the DAYS function in DB2, to convert a date to a consecutive number from the beginning
of the calendar. Since we are dealing with end-of-week dates, we can divide by 7. The result is an absolute sequence number
for each week. The query shown below includes rank and week sequence, as well as their difference, named GRP, which as we
will see has particular significance.
