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?
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.
You can use DATEADD() function as
SELECT *, CONVERT(DATE, DATEADD(Day, RXDAYS, DateShipped)) FirstDateToFind,
CONVERT(DATE, DATEADD(Day, RXDAYS * 2, DateShipped)) SecondDateToFind
FROM
(
VALUES
('2019-06-05', 2, 30)
) T(DateShipped, RemainingDoses, RXDAYS);
UPDATE:
It seems like you're looking for generating rows as the following:
WITH Data AS
(
SELECT *
FROM T
UNION ALL
SELECT CONVERT(DATE, DATEADD(Day, (RXDAYS * (RemainingDoses - RemainingDoses + 1)), DateShipped)),
RemainingDoses - 1,
RXDAYS
FROM Data
WHERE RemainingDoses > 0
)
SELECT *
FROM Data
ORDER BY DateShipped;
Here is a db<>fiddle
Another approach is to make use of a numbers table (or create your own on the fly if you don't already have one). Assuming you can't ever supply more than 255 doses:
DECLARE @Doses table
(
RowID int IDENTITY(1,1),
DateShipped date,
RemainingDoses tinyint,
RXDays tinyint
);
INSERT @Doses(DateShipped, RemainingDoses, RXDays)
VALUES('20190605',2,30),('20190704',3,24);
-- pseudo Numbers table
DECLARE @num table(n tinyint);
INSERT @num(n) SELECT TOP (256)
row_number() OVER (ORDER BY (SELECT NULL)) - 1
FROM sys.all_columns;
SELECT d.RowID, d.DateShipped, d.RemainingDoses, d.RXDays,
SubsequentShipdate = DATEADD(DAY, n.n*d.RXDays, d.DateShipped)
FROM @Doses AS d
INNER JOIN @num AS n
ON n.n <= d.RemainingDoses
ORDER BY d.RowID, SubsequentShipdate;
Results:
If you only want the "next" dates and not include the original row, just add:
AND n.n > 0
Or, don't include 0 in the numbers table (though it can be useful):
INSERT @num(n) SELECT TOP (255)
row_number() OVER (ORDER BY (SELECT NULL))
