SQL -- computing end dates from a given start date with arbitrary breaks

Question

I have a table of 'semesters' of variable lengths with variable breaks in between them with a constraint such that a 'start_date' is always greater than the previous 'end_date':

    id   start_date    end_date
    -----------------------------
     1   2012-10-01   2012-12-20 
     2   2013-01-05   2013-03-28
     3   2013-04-05   2013-06-29
     4   2013-07-10   2013-09-20

And a table of students as follows, where a start date may occur at any time within a given semester:

   id    start_date  n_weeks
   -------------------------
    1    2012-11-15     25
    2    2013-02-12      8 
    3    2013-03-02     12

I am attempting to compute an 'end_date' by joining the 'students' on 'semesters' which takes into account the variable-length breaks in-between semesters.

I can draw in the previous semester's end date (ie from the previous row's end_date) and by subtraction find the number of days in-between semesters using the following:

    SELECT  start_date
          , end_date
          , lag(end_date) OVER () AS prev_end_date
          , start_date - lag(end_date) OVER () AS days_break  
    FROM terms 
    ORDER BY start_date;

Clearly, if there were to be only two terms, it would simply be a matter of adding the 'break' in days (perhaps, cast to 'weeks') -- and thereby extend the 'end_date' by that same period of time.

But should 'n_weeks' for a given student span more than one term, how could such a query be structured ?

Been banging my head against a wall for the last couple of days and I'd be immensely grateful for any help anyone would be able to offer....

Many thanks.

Here's a SQLFiddle with the data and query shown for testing out possible solutions: http://www.sqlfiddle.com/#!12/c487e/2 — IMSoP, Jul 13 '13 at 14:23

score 2 · Accepted Answer · answered Jul 13 '13 at 14:55

Rather than just looking at the lengths of semesters or the gaps between them, you could generate a list of all the dates that are within a semester using generate_series(), like this:

SELECT
  row_number() OVER () as day_number,
  day
FROM
(
  SELECT
    generate_series(start_date, end_date, '1 day') as day
  FROM
    semesters
) as day_series
ORDER BY 
  day

(SQLFiddle demo)

This assigns each day that is during a semester an arbitrary but sequential "day number", skipping out all the gaps between semesters.

You can then use this as a sub-query/CTE JOINed to your table of students: first find the "day number" of their start date, then add 7 * n_weeks to find the "day number" of their end date, and finally join back to find the actual date for that "day number".

This assumes that there is no special handling needed for partial weeks - i.e. if n_weeks is 4, the student must be enrolled for 28 days which are within the duration of a semeseter. The approach could be adapted to measure weeks (pass 1 week as the last argument to generate_series()), with the additional step of finding which week the student's start_date falls into.

Here's a complete query (SQLFiddle demo here):

WITH semester_days AS
(
  SELECT
    semester_id,
    row_number() OVER () as day_number,
    day_date::date
  FROM
  (
    SELECT
      id as semester_id,
      generate_series(start_date, end_date, '1 day') as day_date
    FROM
      semesters
  ) as day_series
  ORDER BY 
    day_date
)
SELECT
  S.id as student_id,
  S.start_date,
  SD_start.semester_id as start_semester_id,
  S.n_weeks,
  SD_end.day_date as end_date,
  SD_end.semester_id as end_semester_id
FROM
  students as S
JOIN
  semester_days as SD_start
  On SD_start.day_date = S.start_date
JOIN
  semester_days as SD_end
  On SD_end.day_number = SD_start.day_number + (7 * S.n_weeks)
ORDER BY
  S.start_date

That is simply awesome. I (and my incipient ulcer) cannot thank you enough!! (and thank you for the detailed explanation -- incredibly useful...) — user908094, Jul 13 '13 at 19:26

SQL -- computing end dates from a given start date with arbitrary breaks

1 Answers1

Linked