CODEWITHSUNDEEP
sqlsql-server
Roger Steinberg
Roger Steinberg

Reputation: 1604

Fill Rows with future dates even when there's no value

Story:

My dataset looks like this:

+---------+------+-----------------+---------+
|  Date   | Cost | Revenue   Month | Revenue |
+---------+------+-----------------+---------+
| 2018-01 |   20 | 2018-02         |      20 |
| 2018-01 |   20 | 2018-03         |     100 |
| 2018-02 |    5 | 2018-03         |      15 |
| 2018-02 |    5 | 2018-04         |      25 |
+---------+------+-----------------+---------+

Basically the Date Column represents initial investment and the Revenue Month is for money generated due to the investment month. I would like to fill rows for the revenue month for each subsequent month until current month and force the Revenue to show 0 (i.e August 2020)

Objective:

+---------+------+-----------------+---------+---------+
|  Date   | Cost | Returning Month | Revenue | Product |
+---------+------+-----------------+---------+---------+
| 2018-01 |   20 | 2018-02         |      20 | A       |
| 2018-01 |   20 | 2018-03         |     100 | A       |
| 2018-01 |   20 | 2018-04         |     0   | A       |
| 2018-01 |   20 | 2018-05         |       0 | A       |
| 2018-02 |    5 | 2018-03         |      15 | A       |
| 2018-02 |    5 | 2018-04         |      25 | A       |
| 2018-02 |   5  | 2018-03         |       0 | A       |
| 2018-02 |   5  | 2018-03         |       0 | A       |

What I tried:

I built this tally date table

DROP TABLE IF EXISTS ##dates
CREATE TABLE ##dates ([date] Date)
DECLARE @dIncr DATE = '01/01/2018'
DECLARE @dEnd DATE = cast(getdate() as date)
WHILE (@dIncr <= @dEnd)
BEGIN
  INSERT INTO ##dates ([date]) VALUES (@dIncr)
  SELECT @dIncr = DATEADD(month,1,@dIncr)
END

But I'm stuck with this.

Upvotes: 0

Views: 173

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

If you want to add two months to the data, you can use union all:

select  Date, Cost, Returning_Month, Revenue, Product 
from t
union all
select  Date, Cost, dateadd(month, v.n, Returning_Month), 0 as Revenue, Product 
from (select date, cost, max(returning_month) as returning_month, revenue, product
      from t
      group by date, cost, revenue, product
     ) t cross apply
     (values (1), (2)) v(n);

EDIT:

Use a recursive CTE:

with cte as (
      select date, cost, max(returning_month) as returning_month, revenue, product, 0 as lev
      from t
      group by date, cost, revenue, product
      union all
      select date, cost, dateadd(month, 1, returning_month), revenue, product, lev + 1
      from cte
      where returning_month < getdate()
     )
select date, cost, returning_month, revenue, product
from cte
where lev > 0;

Upvotes: 1

Related Questions