R/get_qualification_date.R

In crazybilly/ucbudar: A collection of functions and variables for working with data at UC Berkeley UDAR

#' Get Qualification DAte
#'
#' @param db a valid database connection
#'
#' @return a database tibble with one line per proposal and the qualification date calculated in the PROSPECT data sources.
#' @export
get_qualification_date  <- function(db = cdw) {



  tbl(db, sql("

      SELECT
      proposal_id
      , lead_stage_date as qualification_date

      FROM (
        SELECT
        PROPOSAL_ID,
        STAGE_CODE,
        MOVE_ORDER,
        OUTCOME_STAGE_CODE,
        OUTCOME_STAGE_DESC,
        LEAD_MOVE_ORDER,
        CASE
        WHEN LEAD_MOVE_ORDER > MOVE_ORDER THEN 1
        WHEN LEAD_MOVE_ORDER < MOVE_ORDER THEN -1
        ELSE 0
        END AS MOVE_DIRECTION,
        LEAD_STAGE_DATE,
        ROW_NUMBER() OVER (
          PARTITION BY
          D_PROPOSAL_KEY,
          STAGE_CODE,
          CASE
          WHEN LEAD_MOVE_ORDER > MOVE_ORDER THEN 1
          WHEN LEAD_MOVE_ORDER < MOVE_ORDER THEN -1
          ELSE 0
          END
          ORDER BY STAGE_DATE, STAGE_ID
        ) AS STAGE_MOVE_DIRECTION_ROWNUM
        FROM (
          SELECT
          TDE_PROPOSAL_STAGE.D_PROPOSAL_KEY,
          TDE_PROPOSAL_STAGE.PROPOSAL_ID,
          TDE_PROPOSAL_STAGE.STAGE_ID,
          TDE_PROPOSAL_STAGE.STAGE_CODE,
          TDE_PROPOSAL_STAGE.MOVE_ORDER,
          TDE_PROPOSAL_STAGE.STAGE_DATE,
          LEAD(TDE_PROPOSAL_STAGE.STAGE_CODE,1) OVER (
            PARTITION BY TDE_PROPOSAL_STAGE.D_PROPOSAL_KEY
            ORDER BY TRUNC(TDE_PROPOSAL_STAGE.STAGE_DATE), TDE_PROPOSAL_STAGE.STAGE_ADV_ORDER, TDE_PROPOSAL_STAGE.STAGE_ID
          ) AS OUTCOME_STAGE_CODE,
          LEAD(TDE_PROPOSAL_STAGE.STAGE_DESC,1) OVER (
            PARTITION BY TDE_PROPOSAL_STAGE.D_PROPOSAL_KEY
            ORDER BY TRUNC(TDE_PROPOSAL_STAGE.STAGE_DATE), TDE_PROPOSAL_STAGE.STAGE_ADV_ORDER, TDE_PROPOSAL_STAGE.STAGE_ID
          ) AS OUTCOME_STAGE_DESC,
          LEAD(TDE_PROPOSAL_STAGE.MOVE_ORDER,1,0) OVER (
            PARTITION BY TDE_PROPOSAL_STAGE.D_PROPOSAL_KEY
            ORDER BY TRUNC(TDE_PROPOSAL_STAGE.STAGE_DATE), TDE_PROPOSAL_STAGE.STAGE_ADV_ORDER, TDE_PROPOSAL_STAGE.STAGE_ID
          ) AS LEAD_MOVE_ORDER,
          LEAD(TDE_PROPOSAL_STAGE.STAGE_DATE,1) OVER (
            PARTITION BY TDE_PROPOSAL_STAGE.D_PROPOSAL_KEY
            ORDER BY TRUNC(TDE_PROPOSAL_STAGE.STAGE_DATE), TDE_PROPOSAL_STAGE.STAGE_ADV_ORDER, TDE_PROPOSAL_STAGE.STAGE_ID
          ) AS LEAD_STAGE_DATE
          FROM CDWHIST.TDE_PROPOSAL_STAGE
          WHERE
          SYSDATE BETWEEN TDE_PROPOSAL_STAGE.CDW_RECORD_EFFECTIVE_DATE AND TDE_PROPOSAL_STAGE.CDW_RECORD_EXPIRE_DATE
          AND PROPOSAL_ID IS NOT NULL


        )
      ) QUALIFICATION_STAGE
      WHERE
      QUALIFICATION_STAGE.STAGE_CODE = 'QU'
      AND QUALIFICATION_STAGE.MOVE_DIRECTION = 1
      AND QUALIFICATION_STAGE.STAGE_MOVE_DIRECTION_ROWNUM = 1

      ORDER BY PROPOSAL_ID, LEAD_STAGE_DATE
  "))

}