| index

View V_LAST_ENQUIRY_UPDATE

Columns

Name | Type | Optional | Default | Comments
ID | NUMBER |  |  |
MPRN | NUMBER(30) | Y |  |
TRANSACTION_REFERENCE | VARCHAR2(80) | Y |  |
INSTALL_POSTCODE | VARCHAR2(8) | Y |  |
PRTY_ID | NUMBER |  |  |
RT_CODE | VARCHAR2(80) |  |  |
START_DATE | DATE |  |  |
END_DATE | DATE | Y |  |
DESCRIPTION | VARCHAR2(80) | Y |  |
ENQU_ID | NUMBER |  |  |

SQL

    CREATE OR REPLACE VIEW V_LAST_ENQUIRY_UPDATE AS
    SELECT enqu.id,
           enqu.mprn,
           enqu.transaction_reference,
           enqu.install_postcode,
           enro1."PRTY_ID",enro1."RT_CODE",enro1."START_DATE",enro1."END_DATE",enro1."DESCRIPTION",enro1."ENQU_ID"
      FROM enquiries enqu,
           enquiry_roles enro1,
           (SELECT enro.enqu_id, MAX(enro.start_date) max_date
              FROM enquiry_roles enro
             WHERE enro.rt_code = 'AGENT'
             GROUP BY enro.enqu_id) enq_roles
     WHERE enq_roles.enqu_id = enqu.id
       AND enro1.start_date = enq_roles.max_date
       AND enro1.rt_code = 'AGENT'
     ORDER BY enqu.ID;