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
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;