Dynamic Dates SQL Server

Question

I have the below data in SQL.

I need to figure out a way dynamically to get the next dates based the 2 values below. The remaining doses tells me how many dates I will need to find and RXDAYS tells me the increments in which to look for the dates

Date Shipped    Remaining Doses RXDAYS
2019-06-05      2               30

So based on the above I would find the date 30 days after 2019-06-05 and get 2019-07-05 and 30 days after that I would get 2019-08-04.

Is there a way to do this?

Answer 1

Use dateadd(). This gives you the final date:

dateadd(day, doses * rxdays, date_shipped)

On the other hand if you want to generate new rows, then you could use a recursive query:

with cte as (
    select date_shipped mydate, doses, rxdays from mytable
    union all
    select (dateadd(day, rxdays, date_shipped), doses - 1, rxdays 
    from cte
    where doses > 0
)
select mydate from cte

For the sample data row showned in your question, this would generate three rows, with dates 2019-06-05, 2019-07-05 and 2019-08-04.

If doses has values greater than 100, then you need to add option(maxrecursion 0) at the very end of the query.