我有一个要插入新记录的表。对于每个唯一的代码,我想为下个月添加一个新记录,数量为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
您似乎想要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;