插入虚拟行以将缺失值填充到 SQL 表中 [英] Insert dummy rows to fill missing values into a SQL Table

问题描述

我有这个 SQL Server 表 table1，我想用每个帐户的虚拟行填充到最近的上个月结束日期期间，例如现在将到 2021-06-30.

I have this SQL Server table table1 which I want to fill with dummy rows per acct up to latest previous month end date period e.g now would be up to 2021-06-30.

在这个例子中，acct 1 有 n 行，在 2020-05-31 结束，我想为 acct 和 amt begin_date 和 end_date 递增 1 个月，直到 2021 年 6 月 30 日.

In this example, acct 1 has n number of rows which ends at 2020-05-31, and I want to insert dummy rows with same values for acct and amt with begin_date and end_date incrementing by 1 month up to 06-30-2021.

假设 acct 2 已经在 06-30-2021 结束，因此不需要插入虚拟行.

Let's assume acct 2 already ends at 06-30-2021 so this doesn't need dummy rows to be inserted.

 acct,amt,begin_date,end_date
  1 , 10, 2020-04-01, 2020-04-30
  1 , 10, 2020-05-01, 2020-05-31
  2 , 50, 2021-05-01, 2021-05-31
  2 , 50, 2021-06-01, 2021-06-30

因此，对于 acct 1，我希望从 2020 年 5 月 31 日的最后一个时期到上个月结束(现在是 06 年 30 月 2021 年)插入 n 行，并且我希望 amt 和 acct 保持不变.所以它看起来像下面这样:

So for acct 1, I want n number of rows to be inserted from last period of 2020-05-31 up to previous month end which is now 06-30-2021 and I want the amt and acct to remain same. So it would look like this below:

    acct,amt,begin_date,end_date
      1 , 10, 2020-04-01, 2020-04-30
      1 , 10, 2020-05-01, 2020-05-31
      1 , 10, 2020-06-01, 2020-06-30
      1 , 10, 2020-07-01, 2020-07-31
      .............................
      .............................
      1 , 10, 2021-06-01, 2021-06-30

基于一些数据异常，我意识到我需要另一个条件来解决这个问题.假设另一列type 被添加到table1.所以 acct 和 type 将是标识每个相关行的复合键，因此 acct 2 type A 和 acct 2 type B 不相关.所以我们有更新的表:

Based on some data anamolies, I realize I need another condition to the solution. Suppose another column type was added to the table1. So acct and type would be the composite key that identifies each related row hence acct 2 type A and acct 2 type B are not related. So we have the updated table:

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

我现在需要为 acct 2 type B 创建虚拟行，直到 2021-06-30.我们已经知道 acct 2 类型 A 可以，因为它已经有到 2021-06-30 的行

I would now need dummy rows to be created for acct 2 type B up to 2021-06-30. We already know acct 2 type A would be ok since it already has rows up to 2021-06-30

推荐答案

您可以使用递归 CTE 生成行:

You can generate the rows using a recursive CTE:

with cte as (
      select acct, 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 order by end_date desc) as seqnum
            from t
           ) t
      where seqnum = 1 and end_date < '2021-06-30'
      union all
      select acct, amt, dateadd(month, 1, begin_date),
             eomonth(dateadd(month, 1, begin_date))
      from cte
      where begin_date < '2021-06-01'
     )
select *
from cte;

然后您可以使用 insert 将这些行插入到表中.或者，如果您只想要一个包含所有行的结果集，请使用 union all.

You can then use insert to insert these rows into a table. Or use union all if you simply want a result set with all the rows.

这里是db<>fiddle.

Here is a db<>fiddle.

这篇关于插入虚拟行以将缺失值填充到 SQL 表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！