CODEWITHSUNDEEP
sqlsql-serverdatesql-server-2012
Simon
Simon

Reputation: 9021

How can I return the same row for different date occurrences

I'm messing about with a test database table structure to store events with recurrence. I have two tables:

Course

Id          = 1
Title       = Test Course

CourseMeta

Id          = 1
CourseId    = 1
StartDate   = 2018-01-01
RepeatEvery = 7
RepeatAmount = 4

Assuming the values above, what I'm trying to produce (in individual SQL rows) is a list of courses for each date in the range, starting at 2018-01-01 and occurring every 7 days, until there have been 4 iterations. For example, I'm after this output:

Title        Date
-----------  ----------
Test Course  2018-01-01
Test Course  2018-01-08
Test Course  2018-01-15
Test Course  2018-01-22

I'm stuck because I've no idea how to get MS SQL (2012) to show a row multiple times based on a condition. I did have a search and found some information, but nothing which helped so far. Not even sure if this is the best way to store this information.

Upvotes: 0

Views: 40

Answers (2)

Salman Arshad
Salman Arshad

Reputation: 272006

If you have a table of numbers you can do this (uses master..spt_values as the table of numbers):

WITH course(id, title) AS (
    SELECT 1, 'test course'
), coursemeta(courseid , startdate, repeatevery, repeatamount) AS (
    SELECT 1, '2018-01-01', 7, 4
), numbers(number) AS (
    SELECT number FROM master..spt_values WHERE type = 'p'
)
SELECT id, title, DATEADD(day, numbers.number * repeatevery, startdate)
FROM course
INNER JOIN coursemeta ON course.id = coursemeta.courseid
INNER JOIN numbers ON numbers.number < coursemeta.repeatamount

Result:

| id | title       | (No column name)        |
|----|-------------|-------------------------|
| 1  | test course | 2018-01-01 00:00:00.000 |
| 1  | test course | 2018-01-08 00:00:00.000 |
| 1  | test course | 2018-01-15 00:00:00.000 |
| 1  | test course | 2018-01-22 00:00:00.000 |

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269445

You can use a recursive CTE to generate the rows:

with cte as (
      select courseid, startdate, repeatevery, repeatamount, 1 as cnt
      from coursemeta
      union all
      select courseid, dateadd(day, repeatevery, startdate), repeatevery, repeatamount, cnt + 1
      from cte
      where cnt < repeatamount
     )
select courseid, startdate
from cte;

To get the name, you would add a final JOIN.

Note: If repeatamount exceeds 100, add with option (maxrecursion 0).

Upvotes: 1

Related Questions