One row per month between Start and End Dates.

Question

Hello

I’m trying to create a row per month a student was attending class. I’ve got the following tables. Tbl1 has a Students Start and End Dates. Table 2 shows any gaps in study.

What I need is a query that has a row for each month a Student is on the course e.g. even though StudentID 2 has 4 months on, they have 1 month off between 20161101 and 20161201, so I would not need a record for the 1 month off. Just 3 records for the actual on time

Tbl1
StudentID StartDate, End Date MonthsOn
1 20140101 20140531 4
2 20161001 20170901 11
3 20180301 20200901 30

Tbl2
StudentID TimeOffStart, TimeOffEnd MonthsOff
2 20161101 20161201 1
3 20180501 20191001 17

Many thnanks.

Answer 1

Hi @RoyB09 ,

Please also refer below and check whether it is working to you. Thanks.

declare @Tbl1 table ( StudentID int, StartDate date, EndDate date, MonthsOn int )  
      
 insert @Tbl1 values  
 ( 1, '20140101', '20140531', 4  ),  
 ( 2, '20161001', '20170901', 11 ),  
 ( 3, '20180301', '20200901', 30 )  
      
 declare @Tbl2 table ( StudentID int, TimeOffStart date, TimeOffEnd date, MonthsOff int )  
      
 insert @Tbl2 values  
 ( 2, '20161101', '20161201', 1  ),  
 ( 3, '20180501', '20191001', 17 )  
  
 ;with cte as(  
 select StudentID,dateadd(MONTH,v.number,d.StartDate) adate  
  from @Tbl1 d  
  join master..spt_values v on v.type='P'  
  and v.number <= MonthsOn)  
  ,cte1 as (  
   select StudentID,dateadd(MONTH,v.number,d.TimeOffStart) adate  
  from @Tbl2 d  
  join master..spt_values v on v.type='P'  
  and  v.number < MonthsOff)  
  select StudentID,CONVERT(VARCHAR(7), adate, 126) Month from cte  
  except   
  select StudentID,CONVERT(VARCHAR(7), adate, 126) Month from cte1  

Output:

StudentID	Month  
1	2014-01  
1	2014-02  
1	2014-03  
1	2014-04  
1	2014-05  
2	2016-10  
2	2016-12  
2	2017-01  
2	2017-02  
2	2017-03  
2	2017-04  
2	2017-05  
2	2017-06  
2	2017-07  
2	2017-08  
2	2017-09  
3	2018-03  
3	2018-04  
3	2019-10  
3	2019-11  
3	2019-12  
3	2020-01  
3	2020-02  
3	2020-03  
3	2020-04  
3	2020-05  
3	2020-06  
3	2020-07  
3	2020-08  
3	2020-09  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

Answer 2

Guoxiong has already given you a solution, so I will not bother about that. However, I have an article on my web site which discusses this sort of problems, and particularly how you can solve them with a Table of Numbers (of which Guoxiong is using a variation of): http://www.sommarskog.se/Short%20Stories/table-of-numbers.html

Answer 3

Show your variants and check an example:

declare @Tbl1 table ( StudentID int, StartDate date, EndDate date, MonthsOn int )  
  
insert @Tbl1 values  
( 1, '20140101', '20140531', 4  ),  
( 2, '20161001', '20170901', 11 ),  
( 3, '20180301', '20200901', 30 )  
  
declare @Tbl2 table ( StudentID int, TimeOffStart date, TimeOffEnd date, MonthsOff int )  
  
insert @Tbl2 values  
( 2, '20161101', '20161201', 1  ),  
( 3, '20180501', '20191001', 17 )  
  
select * from @Tbl1  
select * from @Tbl2  
  
---  
  
;  
with Q1 as  
(  
    select StudentID, StartDate as d  
    from @Tbl1  
    union all  
    select q.StudentID, DATEADD(m, 1, d)  
    from Q1 q  
    inner join @Tbl1 t on t.StudentID = q.StudentID  
    where d < t.EndDate  
),  
Q2 as  
(  
    select StudentID, TimeOffStart as d  
    from @Tbl2  
    union all  
    select q.StudentID, DATEADD(m, 1, d)  
    from Q2 q  
    inner join @Tbl2 t on t.StudentID = q.StudentID  
    where d < t.TimeOffEnd  
)  
select StudentID, d as MonthOn   
from Q1  
except   
select *  
from Q2  
order by StudentID, d  
option (maxrecursion 0)  

Results:

StudentID MonthOn  
1 2014-01-01  
1 2014-02-01  
1 2014-03-01  
1 2014-04-01  
1 2014-05-01  
1 2014-06-01  
2 2016-10-01  
2 2017-01-01  
2 2017-02-01  
2 2017-03-01  
2 2017-04-01  
2 2017-05-01  
2 2017-06-01  
2 2017-07-01  
2 2017-08-01  
2 2017-09-01  
3 2018-03-01  
3 2018-04-01  
3 2019-11-01  
3 2019-12-01  
3 2020-01-01  
3 2020-02-01  
3 2020-03-01  
3 2020-04-01  
3 2020-05-01  
3 2020-06-01  
3 2020-07-01  
3 2020-08-01  
3 2020-09-01  

Adjust it if EndDate or TimeOffEnd should not be included into intervals.

Answer 4

Thanks for the reply

I'm looking for something along the lines of. where StudendID 1 has a row for all the months and StudentID 2 is missing 2016-10, because she was absent.

I hope this makes sense.

StudentID Month
1 2014-01
1 2014-02
1 2014-03
1 2014-04
1 2014-05
2 2016-10
2 2016-12
2 2017-01
2 2017-02
2 2017-02
2 2017-04
2 2017-05
2 2017-06
2 2017-07
2 2017-08
2 2017-09

Answer 5

DECLARE @Tbl1 TABLE (
    StudentID int, 
    StartDate date, 
    EndDate date, 
    MonthsOn int
);
INSERT INTO @Tbl1 VALUES
(1, '20140101', '20140531', 4),
(2, '20161001', '20170901', 11),
(3, '20180301', '20200901', 30);

DECLARE @Tbl2 TABLE (
    StudentID int, 
    TimeOffStart date, 
    TimeOffEnd date, 
    MonthsOff int
);
INSERT INTO @Tbl2 VALUES
(2, '20161101', '20161201', 1),
(3, '20180501', '20191001', 17);

;WITH CTE_Tbl1 AS (
    SELECT StudentID,  StartDate AS sd
    FROM @Tbl1
    UNION ALL
    SELECT c.StudentID, DATEADD(MONTH, 1, sd)
    FROM CTE_Tbl1 AS c
    INNER JOIN @Tbl1 AS t ON t.StudentID = c.StudentID
    WHERE sd < DATEADD(m, t.MonthsOn, t.StartDate)
),
CTE_Tbl2 AS (
    SELECT StudentID,  TimeOffStart AS sd
    FROM @Tbl2
    UNION ALL
    SELECT c.StudentID, DATEADD(MONTH, 1, sd)
    FROM CTE_Tbl2 AS c
    INNER JOIN @Tbl2 AS t ON t.StudentID = c.StudentID
    WHERE sd < DATEADD(m, t.MonthsOff, t.TimeOffStart)
)

SELECT StudentID, CAST(YEAR(sd) AS varchar(4)) + '-' + RIGHT('0' + CAST(MONTH(sd) AS varchar(4)), 2) AS [Month]
FROM CTE_Tbl1
EXCEPT
SELECT StudentID, CAST(YEAR(sd) AS varchar(4)) + '-' + RIGHT('0' + CAST(MONTH(sd) AS varchar(4)), 2) AS [Month]
FROM CTE_Tbl2
ORDER BY StudentID;