I want to generate multiple rows based on calculation on a column from single record
Example
client reminder_date day generate_reminder
------------------------------------------------
a 2021-10-01 28 4
In the above example generate_reminder is 4 and day is 28. So I want to generate 4 reminder records for every 28 days.
This is the output I needed :
client reminder_date
----------------------
a 2021-10-01
a 2021-10-29
a 2021-11-25
a 2021-12-23
CodePudding user response:
One method would be to use a Tally to generate enough rows to JOIN
to, and then multiple that Tally Number by the number of days, and add that to the first reminder date:
WITH N AS(
SELECT N
FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
SELECT 0 AS I
UNION ALL
SELECT TOP (SELECT MAX(GenerateReminder)-1 FROM dbo.YourTable)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
FROM N N1, N N2) --100 rows Add more cross joins for more rows
SELECT YT.Client,
DATEADD(DAY,[Day] * T.I,YT.ReminderDate) AS ReminderDate
FROM dbo.YourTable YT
JOIN Tally T ON YT.GenerateReminder > T.I;
Note that the results don't match that of your question, as 2021-10-29 28 days is 2021-11-26, not 2021-11-25.
CodePudding user response:
Try a Simple Recursive CTE
CREATE TABLE #Temp
(
CLient VARCHAR(20),
Reminder_date DATE,
Day INT,
generate_reminder int
)
INSERT INTO #Temp
VALUES('A','20201-10-01',28,4)
;WITH CTE
AS
(
SELECT
Client,
Reminder_date,
Day,
generate_reminder
FROM #TEMP
UNION ALL
SELECT
Client,
Reminder_date = DATEADD(DAY,Day,Reminder_date),
Day,
generate_reminder = ISNULL(generate_reminder,0)-1
FROM CTE
WHERE generate_reminder >1
)
SELECT
Client,
Reminder_date
FROM CTE
Here is the link to the fiddle
http://sqlfiddle.com/#!18/18da09/4
CodePudding user response:
This is a compact way to do it especially if you have a reasonable cap on the number of reminders.
select a, dateadd(day, increment, reminder_date) as reminder_date
from tableX t cross apply (
select n * "day" from (values (0), (1), (2), (3), (4), (5)) as v(n)
) ca(increment)
where n < t.generate_reminder;