CODEWITHSUNDEEP
sqlsql-server
john
john

Reputation: 119

Generate multiple records from single record based on aggregation on a column in SQL Server

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

Upvotes: 1

Views: 867

Answers (3)

shawnt00
shawnt00

Reputation: 17915

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;

Upvotes: 0

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

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

Upvotes: 2

Thom A
Thom A

Reputation: 95544

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.

db<>fiddle

Upvotes: 3

Related Questions