温馨提示:本文翻译自stackoverflow.com,查看原文请点击:sql server - SQL insert new record after every unique code with next month as value

sql server - SQL在每个唯一代码之后插入新记录,并将下个月作为值

发布于 2020-03-27 11:15:55

我有一个要插入新记录的表。对于每个唯一的代码,我想为下个月添加一个新记录,数量为0。

输入表示例:

Unique Code |  Date    | Amount 
    1       | 1/4/2015 | 100
    1       | 1/5/2015 | 20
    1       | 1/6/2015 | 30
    1       | 1/7/2015 | 80
    2       | 1/3/2018 | 55
    2       | 1/4/2018 | 60
    2       | 1/5/2018 | 36
    2       | 1/6/2018 | 90

逻辑如下:

If the date < March - 2019 
Then ( Add a new row with Date + 1 month and an amount of 0) 
Else ( do nothing) 

因此输出表应为:

Unique Code |  Date    | Amount 
    1       | 1/4/2015 | 100
    1       | 1/5/2015 | 20
    1       | 1/6/2015 | 30
    1       | 1/7/2015 | 80
    1       | 1/8/2015 | 0
    2       | 1/3/2018 | 55
    2       | 1/4/2018 | 60
    2       | 1/5/2018 | 36
    2       | 1/6/2018 | 90
    2       | 1/7/2018 | 0

查看更多

查看更多

提问者
Data_x99
被浏览
29
Gordon Linoff 2019-07-03 22:36

您似乎想要union all

select uniquecode, date, amount
from t
union all
select uniquecode, dateadd(month, 1, max(date))  0
from t
group by uniquecode
having max(date) < '2019-03-01'
order by uniquecode, date;

发布
问题

分享
好友

手机
浏览

扫码手机浏览