CREATE VIEW "MatterDateStat" AS
SELECT
    "S"."matterNo",
    CAST("D"."date" AS "DateT") AS "date",
    "S"."newStageCode" AS "stage",
    CAST(COALESCE("O"."newStatusCode", 'OP') AS "MatterStatusCodeT") AS "status",
    (
      EXISTS
      (
        SELECT *
          FROM "MatterHistSearch" "MHS1"
          JOIN "MatterEventCode" "MEC1"
            ON ("MEC1"."matterEventCode" = "MHS1"."matterEventCode")
          WHERE "MHS1"."matterNo" = "S"."matterNo"
            AND "MHS1"."date" <= "D"."date"
            AND "MEC1"."newMaintCode" = 'INA'
            AND NOT EXISTS
                (
                  SELECT *
                    FROM "MatterHistSearch" "MHS2"
                    JOIN "MatterEventCode" "MEC2"
                      ON ("MEC2"."matterEventCode" = "MHS2"."matterEventCode")
                    WHERE "MHS2"."matterNo" = "S"."matterNo"
                      AND "MHS2"."date" <= "D"."date"
                      AND ("MHS2"."date", "MHS2"."matterHistRowOrder") > ("MHS1"."date", "MHS1"."matterHistRowOrder")
                      AND "MEC2"."removeMaintCode" = 'INA'
                )
      )
    ) AS "isOnHold",
    "S"."parentMatter",
    "S"."matterHistSeqNo",  -- NULL means that the stage is defaulting from the matter filing date.
    "S"."areaOfLawCode",
    "S"."county",
    "S"."enteredDate",
    "S"."filedDate",
    "S"."grievInvestigator",
    "S"."intakeInvestigator",
    "S"."isSelfNotification",
    "S"."litigationMatterNo",
    "S"."matterType",
    "S"."oldMatterNo",
    "S"."reportMethodCode",
    "S"."respondent",
    "S"."sccaCaseNo",
    "S"."takenBy",
    "L"."isPublic",
    "L"."matterDispoCode",
    "L"."matterDispoDate"
  -- Oldest "filedDate" for a matter in the database is used.  (Older ones not likely to appear now.)
  FROM (SELECT DATE '1974-05-15' + generate_series(0, (CURRENT_DATE - DATE '1974-05-15')) AS "date") "D"
  JOIN "MatterHistStage" "S"
    ON ( "S"."date" <= "D"."date"
     AND NOT EXISTS
         (
           SELECT * FROM "MatterHistStage" "S2"
             WHERE "S2"."matterNo" = "S"."matterNo"
               AND "S2"."date" <= "D"."date"
               AND ("S2"."date", "S2"."matterHistRowOrder") > ("S"."date", "S"."matterHistRowOrder")
         )
       )
  JOIN "Matter" "L"  -- Litigation matter, if present; otherwise the original matter.
    ON ("L"."matterNo" = COALESCE("S"."litigationMatterNo", "S"."matterNo"))
  LEFT JOIN "MatterHistStatus" "O"
    ON ( "O"."matterNo" = "S"."matterNo"
     AND "O"."date" <= "D"."date"
     AND NOT EXISTS
         (
           SELECT * FROM "MatterHistStatus" "O2"
             WHERE "O2"."matterNo" = "O"."matterNo"
               AND "O2"."date" <= "D"."date"
               AND ("O2"."date", "O2"."matterHistRowOrder") > ("O"."date", "O"."matterHistRowOrder")
         )
       )
  WHERE COALESCE("O"."newStatusCode", 'OP') <> 'CL'
;