How can duplicate the data records based on period?

Question

I have a data set as periodic. However, these periods are not consecutive. My data pattern is like that

Period  Customer_No Date        Product
1       111         01.01.2017  X
3       111         05.09.2017  Y
8       111         02.05.2018  Z
6       222         02.02.2017  X
9       222         06.04.2017  Z
12      222         05.09.2018  B
15      222         02.01.2019  A

End of the period should be 15 for all customers. I want to create consecutive periods based on customers and fill them with previous data like below:

Period  Customer_No Date        Product
1       111         01.01.2017  X
2       111         01.01.2017  X
3       111         05.09.2017  Y
4       111         05.09.2017  Y
5       111         05.09.2017  Y
6       111         05.09.2017  Y
7       111         05.09.2017  Y
8       111         02.05.2018  Z
9       111         02.05.2018  Z
10      111         02.05.2018  Z
11      111         02.05.2018  Z
12      111         02.05.2018  Z
13      111         02.05.2018  Z
14      111         02.05.2018  Z
15      111         02.05.2018  Z
6       222         02.02.2017  X
7       222         02.02.2017  X
8       222         02.02.2017  X
9       222         06.04.2017  Z
10      222         06.04.2017  Z
11      222         06.04.2017  Z
12      222         05.09.2018  B
13      222         05.09.2018  B
14      222         05.09.2018  B
15      222         02.01.2019  A


create table tbl_cust(period int,Customer_No int, Date date, Product varchar)

insert into tbl_cust values(1,111,'01.01.2017','X')
insert into tbl_cust values(3,111,'05.09.2017','Y')
insert into tbl_cust values(8,111,'02.05.2018','Z')
insert into tbl_cust values(6,222,'02.02.2017','X')
insert into tbl_cust values(9,222,'06.04.2017','Z')
insert into tbl_cust values(12,222,'05.09.2018','B')
insert into tbl_cust values(15,222,'02.01.2019','A')

Answer 1

You can use a recursive CTE to generate the rows that you want. Then you need to fill them in with the most recent data. What you really want is lag(ignore nulls), but SQL Server does not support that functionality.

There are only up to 15 rows per customer, so apply is a reasonable alternative:

with cte as (
      select min(period) as period, customer_no
      from tbl_cust
      group by customer_no
      union all
      select period + 1, customer_no
      from cte
      where period < 15
     )
select cte.period, cte.customer_no, c.date, c.product
from cte cross apply
     (select top (1) c.*
      from tbl_cust c
      where c.customer_no = cte.customer_no and
            c.period <= cte.period
      order by c.period desc
     ) c
order by cte.customer_no, cte.period;

Here is a db<>fiddle.

Answer 2

You can try this.

select ID as period, Customer_No, [Date], Product from 
    (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15)) P(ID)
        OUTER APPLY( SELECT *, ROW_NUMBER() OVER(PARTITION BY Customer_No ORDER BY period desc) RN 
            FROM tbl_cust C WHERE C.period <= P.ID ) X
WHERE X.RN = 1
ORDER BY Customer_No, ID

Result:

period      Customer_No Date       Product
----------- ----------- ---------- -------
1           111         2017-01-01 X
2           111         2017-01-01 X
3           111         2017-05-09 Y
4           111         2017-05-09 Y
5           111         2017-05-09 Y
6           111         2017-05-09 Y
7           111         2017-05-09 Y
8           111         2018-02-05 Z
9           111         2018-02-05 Z
10          111         2018-02-05 Z
11          111         2018-02-05 Z
12          111         2018-02-05 Z
13          111         2018-02-05 Z
14          111         2018-02-05 Z
15          111         2018-02-05 Z
6           222         2017-02-02 X
7           222         2017-02-02 X
8           222         2017-02-02 X
9           222         2017-06-04 Z
10          222         2017-06-04 Z
11          222         2017-06-04 Z
12          222         2018-05-09 B
13          222         2018-05-09 B
14          222         2018-05-09 B
15          222         2019-02-01 A