Warm tip: This article is reproduced from stackoverflow.com, please click
sql sql-server tsql

SQL insert new record after every unique code with next month as value

发布于 2020-03-27 10:23:37

I have a table that I want to insert new records into. For every unique code, I want to add a new record for the next month with an amount of 0.

Example of input table:

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

The logic goes as such:

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

So the output table should be:

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
Questioner
Data_x99
Viewed
87
Gordon Linoff 2019-07-03 22:36

You seem to want 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;