A row for every month between status updates and untill the end of a period

user_16606548 0
Viewed 47

In the dataset I have a row for every status change per student. They usually go from null > Registered > Intake > Registered or Rejected. The period start at January first and ends at July first.

Table 1

Student_ID Begin_date End_date From_Status To_status
1 20-1-2020 25-1-2020 null Registered
1 25-1-2020 20-2-2020 Registered Intake
1 20-2-2020 20-2-2020 Intake Admitted
2 20-2-2020 25-2-2020 null Registered
2 25-2-2020 4-4-2020 Registered Intake
2 4-4-2020 4-4-2020 Intake Rejected

I want to have a dataset in which I have a row for every month until the first of july. Furthermore, for every month in between status changes.

Desired results:

Date_ID Student_ID Begin_date End_date From_Status To_status
20200101 1 20-1-2020 25-1-2020 null Registered
20200102 1 25-1-2020 20-2-2020 Registered Intake
20200102 1 20-2-2020 20-2-2020 Intake Admitted
20200103 1 20-2-2020 20-2-2020 Intake Admitted
20200104 1 20-2-2020 20-2-2020 Intake Admitted
20200105 1 20-2-2020 20-2-2020 Intake Admitted
20200106 1 20-2-2020 20-2-2020 Intake Admitted
20200102 2 20-2-2020 25-2-2020 null Registered
20200103 2 25-2-2020 4-4-2020 Registered Intake
20200104 2 25-2-2020 4-4-2020 Registered Intake
20200104 2 4-4-2020 4-4-2020 Intake Rejected
20200105 2 4-4-2020 4-4-2020 Intake Rejected
20200106 2 4-4-2020 4-4-2020 Intake Rejected

In my database I am only allowed to create views, so I cannot add a date table.

Any suggestion? Thanks in advance!

sql
1 Answers

Hopefully this could help:

WITH 
    grid AS
        (
            SELECT DISTINCT
                To_Char(t.BEGIN_DATE, 'yyyy') || '01' || LPAD(LEVEL, 2, '0')  "DATE_ID", 
                t.STUDENT_ID "STUDENT_ID",
                CASE WHEN To_Char(t.BEGIN_DATE, 'yyyymm') = To_Char(t.BEGIN_DATE, 'yyyy') || LPAD(LEVEL, 2, '0')  THEN t.BEGIN_DATE END "BEGIN_DATE",
                CASE WHEN To_Char(t.BEGIN_DATE, 'yyyymm') = To_Char(t.BEGIN_DATE, 'yyyy') || LPAD(LEVEL, 2, '0')  THEN t.END_DATE END "END_DATE",
                CASE WHEN To_Char(t.BEGIN_DATE, 'yyyymm') = To_Char(t.BEGIN_DATE, 'yyyy') || LPAD(LEVEL, 2, '0')  THEN t.FROM_STATUS END "FROM_STATUS",
                CASE WHEN To_Char(t.BEGIN_DATE, 'yyyymm') = To_Char(t.BEGIN_DATE, 'yyyy') || LPAD(LEVEL, 2, '0')  THEN t.TO_STATUS END "TO_STATUS"
            FROM
                TABLE1 t
            CONNECT BY
                LEVEL < 7
        ), 
    last_stats AS
        (
            SELECT
                DATE_ID,
                STUDENT_ID,
                BEGIN_DATE,
                END_DATE,
                FROM_STATUS,
                TO_STATUS,
                X_BEGIN_DATE,
                X_END_DATE
            FROM
                (
                    SELECT
                        To_Char(t.BEGIN_DATE, 'yyyy') || '01' || To_Char(t.BEGIN_DATE, 'mm')  "DATE_ID",
                        STUDENT_ID "STUDENT_ID",
                        BEGIN_DATE "BEGIN_DATE",
                        END_DATE "END_DATE",
                        FROM_STATUS,
                        TO_STATUS,
                        Max(BEGIN_DATE) OVER(PARTITION BY STUDENT_ID ORDER BY STUDENT_ID, To_Char(t.BEGIN_DATE, 'yyyy') || '01' || To_Char(t.BEGIN_DATE, 'mm') RANGE UNBOUNDED PRECEDING) "X_BEGIN_DATE",
                        Max(END_DATE) OVER(PARTITION BY STUDENT_ID ORDER BY STUDENT_ID, To_Char(t.END_DATE, 'yyyy') || '01' || To_Char(t.END_DATE, 'mm') RANGE UNBOUNDED PRECEDING) "X_END_DATE"
                    FROM 
                        TABLE1 t
              )
            ORDER BY
                STUDENT_ID,
                DATE_ID,
                BEGIN_DATE
        )
SELECT DISTINCT
    t.DATE_ID "DATE_ID",
    t.STUDENT_ID "STUDENT_ID",
    t.X_BEGIN_DATE "BEGIN_DATE",
    t.X_END_DATE "END_DATE",
    Nvl(t.FROM_STATUS, ls.FROM_STATUS) "FROM_STATUS",
    Nvl(t.TO_STATUS, ls.TO_STATUS) "TO_STATUS"
FROM 
    (
        SELECT
            DATE_ID,
            STUDENT_ID,
            BEGIN_DATE,
            CASE WHEN BEGIN_DATE Is Null THEN  Max(BEGIN_DATE) OVER(PARTITION BY STUDENT_ID ORDER BY STUDENT_ID, DATE_ID RANGE UNBOUNDED PRECEDING) ELSE BEGIN_DATE END "X_BEGIN_DATE",
            CASE WHEN END_DATE Is Null THEN  Max(END_DATE) OVER(PARTITION BY STUDENT_ID ORDER BY STUDENT_ID, DATE_ID RANGE UNBOUNDED PRECEDING) ELSE END_DATE END "X_END_DATE",
            END_DATE,
            FROM_STATUS,
            TO_STATUS
        FROM
            grid
    ORDER BY
      STUDENT_ID,
      DATE_ID
    ) t
LEFT JOIN
    last_stats ls ON(ls.STUDENT_ID = t.STUDENT_ID And ls.BEGIN_DATE = t.X_BEGIN_DATE And ls.END_DATE = t.X_END_DATE)
WHERE
    t.X_BEGIN_DATE Is Not Null And t.X_END_DATE Is Not Null
ORDER BY 
    STUDENT_ID, DATE_ID
user_190233530
Related