CODEWITHSUNDEEP
sqlsql-server
mu_sa
mu_sa

Reputation: 2725

How to add month to a date based on value from another columns

I have a PlannedDate and PeriodLength column. I want to have a new column PlannedMonth. The PlannedMonth should have the result set based on PlannedDate and PeriodLength. So, the PlannedMonth I want to get is 

PlannedMonth is PlannedDate + 0, PlannedDate + 1, ... PlannedDate + (PeriodLength - 1)

I have already achieve something similar

DECLARE @PeriodLength INT = (SELECT PeriodLength FROM IP WHERE Id = [some_id]),
        @index INT = 1
    WHILE (@index <= @PeriodLength)
    BEGIN
        SELECT 
            PlannedDate, 
            DATEADD (mm, @index, PeriodStartDate) AS 'PlannedMonth',
            @index
        FROM
            IP
        WHERE 
            Id = [some_id]
    SET @index = @index + 1 
END

But the problem with this approach is that I am having multiple tables while I want a single table with all the results.

Upvotes: 0

Views: 448

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

I would do this with a recursive CTE:

with cte as (
      select id, PlannedDate, 0 as ind, PeriodLength
      from ip
      union all
      select id, dateadd(1, month, PlannedDate), ind + 1, PeriodLength
      from cte
      where ind <= PeriodLength
     )
select *
from cte;

If you only want one IP, you can add a where clause, either in the outer query or in the first subquery.

Upvotes: 1

Recursive
Recursive

Reputation: 952

This is with a temporary table variable.Correct small mistakes if there comes any.

     DECLARE @tab table(p_date date,p_month date,@index int)
        DECLARE @PeriodLength INT = (SELECT PeriodLength FROM IP WHERE Id = [some_id]),
                @index INT = 1

            WHILE (@index <= @Perio

dLength)
        BEGIN
            insert into @tab SELECT 
                PlannedDate, 
                DATEADD (mm, @index, PeriodStartDate) AS 'PlannedMonth',
                @index
            FROM
                IP
            WHERE 
                Id = [some_id]
        SET @index = @index + 1 
        END
    select * from @tab

Upvotes: 0

Related Questions