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

Question

I have a variable length “semester” table with variable gaps between them with a constraint, so that “start_date” is always larger 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 the students table is as follows, where the start date can occur at any time during this semester:

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

I am trying to compute the "end_date" by attaching the "students" to the "semesters", which takes into account variable length gaps between semesters.

I can draw the previous end date of the semester (i.e. from the previous line end_date) and subtract to find the number of days 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;

Obviously, if only two members existed, it was just a matter of adding a “break” in days (possibly dropping to “weeks”) and thereby extending the “end_date” to the same time period.

But should n_weeks for a given student cover more than one term, how can such a request be structured?

I hit my head against the wall for the last couple of days, and I would be very grateful for any help anyone could offer ....

Many thanks.

Answer 1

, , , , generate_series(), :

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)

, , " ", .

/CTE JOIN ed : " " " , 7 * n_weeks, " " , , , , " ".

, , .. n_weeks 4, 28 , . ( 1 week generate_series()), , start_date.

( SQLFiddle ):

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