{% set source_columns = ["Field_P_Id","Field_P_Name", "Field_P_Alias", "Field_P_Type", "Field_P_Required", "Field_P_Max", "Field_P_Min", "Field_P_DecimalFraction", "Field_P_ReferTo", "Field_P_ReferTo", "Field_P_ReferVia","time"] %}

SELECT
  {{ source_columns[0]  ~ " AS " ~ source_columns[0]  | replace("_P", "") | lower }}
  {% for col in source_columns[1:] %}
      {% if col == 'time' %}
        {{ ",MAX(time) AS insert_at" }}
      {% else %}
        {{ ",MAX_BY(" ~ col ~ ",time) AS " ~ col | replace("_P", "") | lower }}
      {% endif %}
  {% endfor %}
FROM {{ source('porters', 'field') }}
GROUP BY field_id

SELECT
    Field_P_Id AS field_id
    ,MAX_BY(Field_P_Name,time) AS field_name
    ,MAX_BY(Field_P_Alias,time) AS field_alias
    ,MAX_BY(Field_P_Type,time) AS field_type
    ,MAX_BY(Field_P_Required,time) AS field_required
    ,MAX_BY(Field_P_Max,time) AS field_max
    ,MAX_BY(Field_P_Min,time) AS field_min
    ,MAX_BY(Field_P_DecimalFraction,time) AS field_decimalfraction
    ,MAX_BY(Field_P_ReferTo,time) AS field_referto
    ,MAX_BY(Field_P_ReferTo,time) AS field_referto
    ,MAX_BY(Field_P_ReferVia,time) AS field_refervia
    ,MAX(time) AS insert_at
FROM `project_id`.`porters`.`field`
GROUP BY field_id