Recursive Query with CTE I

I recently ran into a problem that I found interesting enough to put on my blog. It covers a common situation where you use a dataset several times, also called a recursive query. Someone asked for the number of weeks worked per period per temp worker.

Per period someone can have several timesheets, and each one records the number of “weeks worked”. To find how many weeks someone worked in a period, we take the last “weeks worked” in a period and subtract the last “weeks worked” from the previous period. Sounds simple, right?

The image below shows the result we will use as the basis to find the “weeks worked” per period:

We grouped our data by year, Periode444Nummer and krachtcode, and take the MAX(WekenGewerkt) per period. If we now want to know how many weeks someone worked in period 6-2008, we have to calculate 76-72. After all, at the end of period 6-2008 someone had worked 76 weeks, and at the end of period 5-2008 72 weeks. That difference is what he worked in period 6.

To make all this possible, we generate the standard dataset above, which we can then reuse. This is also called a Common Table Expression (CTE). We also add a row number per krachtcode and sort it by krachtcode, year and period number. And we need the previous year too, to find how many weeks someone worked in the first period of the current year! This gives a result that looks something like this:

The sorting puts everything neatly in order, so later we can easily subtract the weeks from each other!

Next we write the query below and run it on the dataset we generated earlier:

The key is the part with the circle around it. Here we join the table to itself, so we can find the result of the previous week worked. We subtract that from the current week, and that gives the result.

The complete query looks something like this:

Of course you can use this example in many more situations, for example to calculate cumulative totals.