As soon as a new project reaches dcp_name="Prepare Filed Land Use Application" in the dcp_milestone table, the project id should be used to retrieve the geometries and write them to the project_geoms table.
Starter SQL 1:
SELECT distinct [projectid] FROM dcp_milestone
WHERE
dcp_name = 'Prepare Filed Land Use Application'
AND [projectid] NOT IN (SELECT projectid FROM project_geoms)
Starter SQL 2:
SELECT json_agg(json_build_object(
'dcp_name', m.dcp_name,
'milestonename', m.milestonename,
'dcp_plannedstartdate', m.dcp_plannedstartdate,
'dcp_plannedcompletiondate', m.dcp_plannedcompletiondate,
'dcp_actualstartdate', m.dcp_actualstartdate,
'dcp_actualenddate', m.dcp_actualenddate,
'statuscode', m.statuscode,
'dcp_milestonesequence', m.dcp_milestonesequence,
'outcome', m.outcome
))
FROM (
SELECT
mm.*,
dcp_milestone.dcp_name AS milestonename,
dcp_milestoneoutcome.dcp_name AS outcome
FROM dcp_projectmilestone mm
LEFT JOIN dcp_milestone
ON mm.dcp_milestone = dcp_milestone.dcp_milestoneid
LEFT JOIN dcp_milestoneoutcome
ON mm.dcp_milestoneoutcome = dcp_milestoneoutcomeid
WHERE mm.dcp_project = p.dcp_projectid
ORDER BY mm.dcp_milestonesequence ASC
) m
WHERE milestonename = 'Prepare Filed Land Use Application'
AND statuscode <> 'Overridden'
As soon as a new project reaches dcp_name="Prepare Filed Land Use Application" in the
dcp_milestonetable, the project id should be used to retrieve the geometries and write them to theproject_geomstable.Starter SQL 1:
SELECT distinct [projectid] FROM dcp_milestone WHERE dcp_name = 'Prepare Filed Land Use Application' AND [projectid] NOT IN (SELECT projectid FROM project_geoms)
Starter SQL 2:
SELECT json_agg(json_build_object( 'dcp_name', m.dcp_name, 'milestonename', m.milestonename, 'dcp_plannedstartdate', m.dcp_plannedstartdate, 'dcp_plannedcompletiondate', m.dcp_plannedcompletiondate, 'dcp_actualstartdate', m.dcp_actualstartdate, 'dcp_actualenddate', m.dcp_actualenddate, 'statuscode', m.statuscode, 'dcp_milestonesequence', m.dcp_milestonesequence, 'outcome', m.outcome )) FROM ( SELECT mm.*, dcp_milestone.dcp_name AS milestonename, dcp_milestoneoutcome.dcp_name AS outcome FROM dcp_projectmilestone mm LEFT JOIN dcp_milestone ON mm.dcp_milestone = dcp_milestone.dcp_milestoneid LEFT JOIN dcp_milestoneoutcome ON mm.dcp_milestoneoutcome = dcp_milestoneoutcomeid WHERE mm.dcp_project = p.dcp_projectid ORDER BY mm.dcp_milestonesequence ASC ) m WHERE milestonename = 'Prepare Filed Land Use Application' AND statuscode <> 'Overridden'