Skip to main content
You can succeed best and quickest by helping others to succeed. Join the conversation.
Showing results for 
Search instead for 
Did you mean: 
Contributor II
Contributor II

Extract all year dates between two dates in a table

Hi Community,


I have the following data:

Article ID Price Price is valid from Price is valid to
1 3 01.01.2019 01.06.2019
1 4 01.07.2019 01.12.2019
1 2 01.12.2019 01.01.2021
1 5 01.01.2021 01.01.2022


Now I want to create a table with the following content:

Article ID Avg-Price in 2022 Avg-Price in 2021 Avg-Prive in 2020 Avg-Price in 2019


The problem is, I have years which are not mentioned in the data. In this case, for instance, year 2020 is not mentioned in the data, but has the price "2", because of "01.12.2019 to 01.01.2021". How can I solve this problem?

Is there a way to "split" each row in the data-table into multiple rows by containing year in the "valid from to valid to"-range?

For Example, lets look at row 3 in data-table:

Article ID Price Price is valid from Price is valid to
1 2 01.12.2019 01.01.2021


I want to "split" this row in 3 different row, because this price is valid for 3 years:

Article ID Price Price is valid from Price is valid to year
1 2 01.12.2019 01.01.2021 2019
1 2 01.12.2019 01.01.2021 2020
1 2 01.12.2019 01.01.2021 2021


Now i could calculate the avg price for all years because I have all relevant years in the new column "year"


Can someone please help me?


Thank you!



Labels (2)
4 Replies
Contributor II
Contributor II

Thank you


I see, that I can use a LOAD statement within a LOOP. 

But how can I use a LOOP within a LOAD statement?


In my example mentioned above we have row number 3 (table 1) which results in 3 new rows (table 4), because the columns from - to are 2019 to 2021, which means I need the row with each 2019, 2020 and 2021.


I am still confused as a beginner.



Former Employee
Former Employee

The structure of a Load within a Loop is:
For ...
   Load ...

The structure of a Loop within a Load is:
Load ...
   From ...
   While ...;

The first blog post addresses both types.


@fatihu  try below

Load [Article ID],
     Year(Price_Valid_From)+IterNo()-1 as Price_Valid_Year
FROM Source
while Year(Price_Valid_From)+IterNo()-1<=year(Price_Valid_To);