I recently went to an interesting question to which I found to my blog. It addresses a common situation where you have a dataset used several times, also called a recursive query. It said the number of weeks worked per period per temp.

Each time a person can have multiple statements with the number "weeks worked" is kept. To determine how much someone in a period of weeks worked should we go the last "weeks worked" in one period and drawing since the last "weeks worked" from the previous period from, sounds simple right?

Figure below shows the result we will use as the basis for "weeks worked" per period to provide evidence:

We have grouped our data by year, Periode444Nummer, power code and take the MAX (Weeks Worked) per period. If we want to know how many weeks a person worked in period 6-2008, we must execute 76-72. Someone finally at the end of period 6-2008 76 weeks worked and at the end of period 5-2008 72 weeks worked. That difference is what he has worked in period 6.

All this possible we generate above standard dataset that we can then reuse, also called Common Table Expression (CTE). Also we add a row number to a power to force this sort code and code, year and period number. Furthermore, we also need the previous years to determine how many weeks a person worked in the first period of the current year! This gives a result that looks something like this:


The size makes sure everything is in order so that we will soon easily be able to deduct weeks apart!

Then we make the following query and execute it on the dataset generated from previous:



The strength lies in part with the circle around it. Here we join the table itself so that we the result of last week to find work. This we draw from the current week and are thus the result.

The complete query looks something like:


Obviously this example you can use in many more situations, eg for calculating cumulative.