Create missing records for accounts per period

[dapostolopoylos]

I have this requirement that i would like to share with you in case you can find me a solution:

I load accounts and ballances for three consequent months. Some accounts may be open in the first month but they could be closed in the second and third so they do not exist in the dataset of the last two months...

What i want is to create the missing records for every account in every period and for the missing periods fill the balance field with the value of the last period populated.

A small example:

Period 1

---------------

Acc, Period, Balance

a     ,201405,     10

b     ,201405,     20

c     ,201405,     15

Period 2

---------------

Acc, Period, Balance

a     ,201406,     10

b     ,201406,     30

d     ,201406,     40

Period 3

---------------

Acc, Period, Balance

a     ,201407,     20

b     ,201407,     30

e     ,201407,     50

What  i want to create is the following table:

All Periods

------------------

Acc, Period, Balance, Activity_Flag

a     ,201405,     10,     Y

b     ,201405,     20,     Y

c     ,201405,     15,     Y

a     ,201406,     10,     Y

b     ,201406,     30,     Y

c     ,201406,     15,     N

d     ,201406,     40,     Y

a     ,201407,     20,     Y

b     ,201407,     30,     Y

c     ,201407,     15,     N

d     ,201407,     40,     N

e     ,201407,     50,     Y

I have already read this article but i cannot adapt it in order to work for my case...

Any ideas???

[Anonymous]

Hi Sergey

Thanks very much for your reply. I included the +10 for testing purposes but do not require it in the use case. When I perform the reload, the missing dates are not filled in?

My goal is to have 2 entries (one for each sku_id) for each date. (A total of 10 entries) [provided we remove the +10..]

Thanks very much

Shaun