CODEWITHSUNDEEP
sqlcommon-table-expression
RohanDsouza
RohanDsouza

Reputation: 412

SQL CTE for multiple records

I have a table as follows:

StudentID CourseID    StartDate    EndDate
    1        1       12/21/2012    12/23/2012
    1        2       12/31/2012    1/2/2013
    2        1       12/10/2012    12/10/2012

How can I get output like following (either using CTE or any other technique)

StudentID CourseID    Date
   1         1      12/21/2012
   1         1      12/22/2012
   1         1      12/23/2012
   1         2      12/31/2012
   1         2      01/01/2013
   1         2      01/02/2013
   2         1      12/10/2012

Upvotes: 0

Views: 163

Answers (1)

Taryn
Taryn

Reputation: 247680

You can use the following:

;with dates(studentid, courseid, date, enddate) as
(
  select studentid, courseid, startdate, enddate
  from yourtable
  union all
  select studentid, courseid, dateadd(day, 1, date), enddate
  from dates
  where dateadd(day, 1, date) <= enddate
)
select studentid, courseid, date
from dates
order by studentid, courseid;

See SQL Fiddle with Demo

The result is:

| STUDENTID | COURSEID |       DATE |
-------------------------------------
|         1 |        1 | 12/21/2012 |
|         1 |        1 | 12/22/2012 |
|         1 |        1 | 12/23/2012 |
|         1 |        2 | 12/31/2012 |
|         1 |        2 | 01/01/2013 |
|         1 |        2 | 01/02/2013 |
|         2 |        1 | 12/10/2012 |

Upvotes: 2

Related Questions