One row per month between Start and End Dates.

RoyB09 306 Reputation points
2020-11-25T17:39:52.673+00:00

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.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,547 questions
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,176 Reputation points
    2020-11-26T05:54:31.807+00:00

    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

    0 comments No comments

4 additional answers

Sort by: Most helpful
  1. Erland Sommarskog 100.8K Reputation points MVP
    2020-11-25T22:57:27.547+00:00

    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

    0 comments No comments

  2. Viorel 111.8K Reputation points
    2020-11-25T19:12:13.63+00:00

    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.

    0 comments No comments

  3. RoyB09 306 Reputation points
    2020-11-25T19:42:48.59+00:00

    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

    0 comments No comments

  4. Guoxiong 8,126 Reputation points
    2020-11-25T20:14:55.98+00:00
    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;
    
    0 comments No comments