Create dummy rows to fill missing values into a SQL Server table

Question

This question is in addition to my previous post: Insert dummy rows to fill missing values into a SQL Table

I have this SQL Server table; I want to fill with n number of missing rows with dummy data up to the previous month end, which at this time would be 2021-06-30. I want the other columns acct, type, amt to be the same with begin_date and end_date incrementing by 1 month up to 2021-06-30.

For example: acct 2 type B ends at period 2021-02-28, hence I need dummy rows to be created from this date up to the end of the previous month end which as of now is 2021-06-30.

acct 2 type A already has rows up to the previous months end 2021-06-30 hence no dummy rows are needed.

 acct,type,amt, begin_date, end_date
  1,  C,   10, 2020-05-01, 2020-05-31
  1,  C,   10, 2020-06-01, 2020-06-30
  2,  B,   50 ,2021-01-01, 2021-01-31
  2,  B,   50 ,2021-02-01, 2021-02-28
  2,  A,   50 ,2021-05-01, 2021-05-31
  2,  A,   50 ,2021-06-01, 2021-06-30

This is how I want the result:

      acct,type,amt, begin_date, end_date
      1,  C,   10, 2020-05-01, 2020-05-31
      1,  C,   10, 2020-06-01, 2020-06-30
      ....................................
      1,  C,   10, 2021-06-01, 2021-06-30
      2,  B,   50 ,2021-01-01, 2021-01-31
      2,  B,   50 ,2021-02-01, 2021-02-28
      2,  B,   50 ,2021-03-01, 2021-03-31
      2,  B,   50 ,2021-04-01, 2021-04-30
      2,  B,   50 ,2021-05-01, 2021-05-31
      2,  B,   50 ,2021-06-01, 2021-06-30

Then I would be able to do a insert to the original table or a union all

Answer 1

According to your data, 1/C ends a year before the date you have in mind. So, it should also generate rows.

I see the difference from your previous question:

• You have acct/type together.
• The cutoff date is dynamic based on the current date.

The changes are really just tweaks on the earlier query:

with cte as (
      select acct, type, amt,
             dateadd(day, 1, end_date) as begin_date,
             eomonth(dateadd(day, 1, end_date)) as end_date
      from (select t.*,
                   row_number() over (partition by acct, type order by end_date desc) as seqnum
            from t
           ) t
      where seqnum = 1 and end_date < eomonth(getdate(), -2)
      union all
      select acct, type, amt, dateadd(month, 1, begin_date),
             eomonth(dateadd(month, 1, begin_date))
      from cte
      where begin_date < eomonth(getdate(), -2)
     )
select *
from cte;

Here is a db<>fiddle.