Missing approval information PGSQL: Difference between revisions

From IMSMA Wiki
Jump to navigation Jump to search
Created page with "{{Under construction | still MySQL queries.}} It is '''important''' that the approval information is set. '''NOTE''' How to fix it is different for each country <pre> SELECT..."
 
No edit summary
 
(4 intermediate revisions by the same user not shown)
Line 1: Line 1:
{{Under construction | still MySQL queries.}}
{{TOC right}}
It is '''important''' that the approval information is set. '''NOTE''' How to fix it is different for each country
[[Image:Missing approval info.png|800px|center]]
<div align="center">
'' How it should NOT look like''
</div>
Depending on the reason why the approval information is missing there are different combinations:
* only Approval date missing
* only Approved by missing
* both missing.
 
Common user in ''fieldreport.dataenterer'' when the data has been entered by using migration/import scripts is migration/import.
 
The solution is of course depending on what is missing. It is '''important''' that the approval information is set and '''only''' rows with workbench status = '''Approved''' are included. 
 
==Missing approval information ==__NOEDITSECTION__
<pre>
<pre>
SELECT
SELECT
fieldreport.fieldreport_localid,
"public".fieldreport.fieldreport_localid AS "Form ID",
fieldreport.dateofreport AS `Date of Information`,
"public".fieldreport.dateofreport AS "Date of Information",
fieldreport.reportreceiveddate AS `Data Entry Date`,
"public".fieldreport.reportreceiveddate AS "Data Entry Date",
fieldreport.reportcompleteddate AS `Submitted Date`,
"public".fieldreport.reportcompleteddate AS "Submitted Date",
fieldreport.reportverifieddate AS `Approved Date`,
"public".fieldreport.reportverifieddate AS "Approved Date",
fieldreport.dataenterer AS `Data enterer`,
"public".fieldreport.verifiedby AS "Approved by",
fieldreport.verifiedby AS `Approved by`
"public".fieldreport.dataenterer AS "FR Data enterer",
imsmaenum.enumvalue
FROM
FROM
fieldreport
fieldreport
Inner Join imsmaenum AS workbench_status ON (workbench_status.imsmaenum_guid = fieldreport.workbenchstatusenum_guid)
Inner Join imsmaenum ON (imsmaenum.imsmaenum_guid = fieldreport.workbenchstatusenum_guid)
WHERE
WHERE
workbench_status.enumvalue =  'Approved' AND
imsmaenum.enumvalue =  'Approved' AND
(fieldreport.reportverifieddate IS NULL  OR
(fieldreport.reportverifieddate IS NULL  OR
fieldreport.verifiedby IS NULL )
fieldreport.verifiedby IS NULL )
ORDER BY
ORDER BY
fieldreport.fieldreport_localid ASC
"Form ID" ASC
</pre>
 
If you have any output here you need to take action. The queries below help you to identify for which item(s) the rows in the table ''fieldreport'' need to be updated.
 
=== Accident===__NOEDITSECTION__
<pre>
SELECT
"public".fieldreport.fieldreport_localid AS "Form ID",
"public".fieldreport.dateofreport AS "Date of Information",
"public".fieldreport.reportreceiveddate AS "Data Entry Date",
"public".fieldreport.reportcompleteddate AS "Submitted Date",
"public".fieldreport.reportverifieddate AS "Approved Date",
"public".fieldreport.verifiedby AS "Approved by",
"public".imsmaenum.enumvalue,
"public".fieldreport.dataentrydate AS "FR Data date",
"public".fieldreport.dataenterer AS "FR Data enterer",
"public".accidentinfoversion.dataentrydate AS "InfoDate",
"public".accidentinfoversion.dataenterer AS "InfoEnterer"
FROM
fieldreport
INNER JOIN imsmaenum ON imsmaenum.imsmaenum_guid = fieldreport.workbenchstatusenum_guid
INNER JOIN accidentinfoversion ON accidentinfoversion.fieldreport_guid = fieldreport.fieldreport_guid
WHERE
imsmaenum.enumvalue = 'Approved' AND
(fieldreport.reportverifieddate IS NULL OR
fieldreport.verifiedby IS NULL) AND
accidentinfoversion.link_only = 'f'
ORDER BY
"Date of Information" ASC
</pre>
=== Hazard ===__NOEDITSECTION__
<pre>
SELECT
"public".fieldreport.fieldreport_localid AS "Form ID",
"public".fieldreport.dateofreport AS "Date of Information",
"public".fieldreport.reportreceiveddate AS "Data Entry Date",
"public".fieldreport.reportcompleteddate AS "Submitted Date",
"public".fieldreport.reportverifieddate AS "Approved Date",
"public".fieldreport.verifiedby AS "Approved by",
"public".imsmaenum.enumvalue,
"public".fieldreport.dataentrydate AS "FR Data date",
"public".fieldreport.dataenterer AS "FR Data enterer",
"public".hazardinfoversion.dataentrydate AS "InfoDate",
"public".hazardinfoversion.dataenterer AS "InfoEnterer"
FROM
fieldreport
INNER JOIN imsmaenum  ON imsmaenum.imsmaenum_guid = fieldreport.workbenchstatusenum_guid
INNER JOIN hazardinfoversion ON fieldreport.fieldreport_guid = hazardinfoversion.fieldreport_guid
WHERE
imsmaenum.enumvalue = 'Approved' AND
(fieldreport.reportverifieddate IS NULL OR
fieldreport.verifiedby IS NULL) AND
hazardinfoversion.link_only = 'f'
ORDER BY
"Date of Information" ASC
</pre>
=== Hazard Reducation===__NOEDITSECTION__
<pre>
SELECT
"public".fieldreport.fieldreport_localid AS "Form ID",
"public".fieldreport.dateofreport AS "Date of Information",
"public".fieldreport.reportreceiveddate AS "Data Entry Date",
"public".fieldreport.reportcompleteddate AS "Submitted Date",
"public".fieldreport.reportverifieddate AS "Approved Date",
"public".fieldreport.verifiedby AS "Approved by",
"public".imsmaenum.enumvalue,
"public".fieldreport.dataentrydate AS "FR Data date",
"public".fieldreport.dataenterer AS "FR Data enterer",
"public".hazreducinfoversion.dataentrydate AS "InfoDate",
"public".hazreducinfoversion.dataenterer AS "InfoEnterer"
FROM
fieldreport
INNER JOIN imsmaenum  ON imsmaenum.imsmaenum_guid = fieldreport.workbenchstatusenum_guid
INNER JOIN hazreducinfoversion ON fieldreport.fieldreport_guid = hazreducinfoversion.fieldreport_guid
WHERE
imsmaenum.enumvalue = 'Approved' AND
(fieldreport.reportverifieddate IS NULL OR
fieldreport.verifiedby IS NULL) AND
hazreducinfoversion.link_only = 'f'
ORDER BY
"Date of Information" ASC
</pre>
=== Location===__NOEDITSECTION__
<pre>
SELECT
"public".fieldreport.fieldreport_localid AS "Form ID",
"public".fieldreport.dateofreport AS "Date of Information",
"public".fieldreport.reportreceiveddate AS "Data Entry Date",
"public".fieldreport.reportcompleteddate AS "Submitted Date",
"public".fieldreport.reportverifieddate AS "Approved Date",
"public".fieldreport.verifiedby AS "Approved by",
"public".imsmaenum.enumvalue,
"public".fieldreport.dataentrydate AS "FR Data date",
"public".fieldreport.dataenterer AS "FR Data enterer",
"public".locationinfoversion.dataentrydate AS "InfoDate",
"public".locationinfoversion.dataenterer AS "InfoEnterer"
FROM
fieldreport
INNER JOIN imsmaenum  ON imsmaenum.imsmaenum_guid = fieldreport.workbenchstatusenum_guid
INNER JOIN locationinfoversion ON fieldreport.fieldreport_guid = locationinfoversion.fieldreport_guid
WHERE
imsmaenum.enumvalue = 'Approved' AND
(fieldreport.reportverifieddate IS NULL OR
fieldreport.verifiedby IS NULL) AND
locationinfoversion.link_only = 'f'
ORDER BY
"Date of Information" ASC
</pre>
=== MRE===__NOEDITSECTION__
<pre>
SELECT
"public".fieldreport.fieldreport_localid AS "Form ID",
"public".fieldreport.dateofreport AS "Date of Information",
"public".fieldreport.reportreceiveddate AS "Data Entry Date",
"public".fieldreport.reportcompleteddate AS "Submitted Date",
"public".fieldreport.reportverifieddate AS "Approved Date",
"public".fieldreport.verifiedby AS "Approved by",
"public".imsmaenum.enumvalue,
"public".fieldreport.dataentrydate AS "FR Data date",
"public".fieldreport.dataenterer AS "FR Data enterer",
"public".mreinfoversion.dataentrydate AS "InfoDate",
"public".mreinfoversion.dataenterer AS "InfoEnterer"
FROM
fieldreport
INNER JOIN imsmaenum  ON imsmaenum.imsmaenum_guid = fieldreport.workbenchstatusenum_guid
INNER JOIN mreinfoversion ON fieldreport.fieldreport_guid = mreinfoversion.fieldreport_guid
WHERE
imsmaenum.enumvalue = 'Approved' AND
(fieldreport.reportverifieddate IS NULL OR
fieldreport.verifiedby IS NULL) AND
mreinfoversion.link_only = 'f'
ORDER BY
"Date of Information" ASC
</pre>
=== QM===__NOEDITSECTION__
<pre>
SELECT
"public".fieldreport.fieldreport_localid AS "Form ID",
"public".fieldreport.dateofreport AS "Date of Information",
"public".fieldreport.reportreceiveddate AS "Data Entry Date",
"public".fieldreport.reportcompleteddate AS "Submitted Date",
"public".fieldreport.reportverifieddate AS "Approved Date",
"public".fieldreport.verifiedby AS "Approved by",
"public".imsmaenum.enumvalue,
"public".fieldreport.dataentrydate AS "FR Data date",
"public".fieldreport.dataenterer AS "FR Data enterer",
"public".qainfoversion.dataentrydate AS "InfoDate",
"public".qainfoversion.dataenterer AS "InfoEnterer"
FROM
fieldreport
INNER JOIN imsmaenum  ON imsmaenum.imsmaenum_guid = fieldreport.workbenchstatusenum_guid
INNER JOIN qainfoversion ON fieldreport.fieldreport_guid = qainfoversion.fieldreport_guid
WHERE
imsmaenum.enumvalue = 'Approved' AND
(fieldreport.reportverifieddate IS NULL OR
fieldreport.verifiedby IS NULL) AND
qainfoversion.link_only = 'f'
ORDER BY
"Date of Information" ASC
</pre>
=== Victim===__NOEDITSECTION__
<pre>
SELECT
"public".fieldreport.fieldreport_localid AS "Form ID",
"public".fieldreport.dateofreport AS "Date of Information",
"public".fieldreport.reportreceiveddate AS "Data Entry Date",
"public".fieldreport.reportcompleteddate AS "Submitted Date",
"public".fieldreport.reportverifieddate AS "Approved Date",
"public".fieldreport.verifiedby AS "Approved by",
"public".imsmaenum.enumvalue,
"public".fieldreport.dataentrydate AS "FR Data date",
"public".fieldreport.dataenterer AS "FR Data enterer",
"public".victiminfoversion.dataentrydate AS "InfoDate",
"public".victiminfoversion.dataenterer AS "InfoEnterer"
FROM
fieldreport
INNER JOIN imsmaenum  ON imsmaenum.imsmaenum_guid = fieldreport.workbenchstatusenum_guid
INNER JOIN victiminfoversion ON fieldreport.fieldreport_guid = victiminfoversion.fieldreport_guid
WHERE
imsmaenum.enumvalue = 'Approved' AND
(fieldreport.reportverifieddate IS NULL OR
fieldreport.verifiedby IS NULL) AND
victiminfoversion.link_only = 'f'
ORDER BY
"Date of Information" ASC
</pre>
 
== Example update query==__NOEDITSECTION__
<pre>
UPDATE fieldreport
SET
reportverifieddate = hazreducinfoversion.dataentrydate
FROM imsmaenum, hazreducinfoversion
WHERE
imsmaenum.imsmaenum_guid = fieldreport.workbenchstatusenum_guid AND
hazreducinfoversion.fieldreport_guid = fieldreport.fieldreport_guid AND
imsmaenum.enumvalue = 'Approved' AND
fieldreport.reportverifieddate IS NULL AND
hazreducinfoversion.link_only = 'f' AND
dateofreport is not NULL AND
dateofreport < hazreducinfoversion.dataentrydate
 
UPDATE fieldreport
SET
reportverifieddate = dateofreport
FROM imsmaenum, hazreducinfoversion
WHERE
imsmaenum.imsmaenum_guid = fieldreport.workbenchstatusenum_guid AND
hazreducinfoversion.fieldreport_guid = fieldreport.fieldreport_guid AND
imsmaenum.enumvalue = 'Approved' AND
fieldreport.reportverifieddate IS NULL AND
hazreducinfoversion.link_only = 'f' AND
dateofreport is not NULL AND
dateofreport > hazreducinfoversion.dataentrydate
 
update fieldreport
SET
verifiedby = fieldreport.dataenterer
FROM imsmaenum, hazreducinfoversion
WHERE
imsmaenum.imsmaenum_guid = fieldreport.workbenchstatusenum_guid AND
hazreducinfoversion.fieldreport_guid = fieldreport.fieldreport_guid AND
imsmaenum.enumvalue =  'Approved' AND
fieldreport.verifiedby IS NULL  AND
hazreducinfoversion.link_only =  'f' and
fieldreport.dataenterer is not null
 
</pre>
</pre>
{{NavBox Hub}}
{{NavBox Hub}}
[[Category:NoPublic]]
[[Category:NoPublic]]
[[Category:SQL Queries]]
[[Category:SQL Queries]]

Latest revision as of 22:17, 5 December 2016

How it should NOT look like

Depending on the reason why the approval information is missing there are different combinations:

  • only Approval date missing
  • only Approved by missing
  • both missing.

Common user in fieldreport.dataenterer when the data has been entered by using migration/import scripts is migration/import.

The solution is of course depending on what is missing. It is important that the approval information is set and only rows with workbench status = Approved are included.

Missing approval information

SELECT
"public".fieldreport.fieldreport_localid AS "Form ID",
"public".fieldreport.dateofreport AS "Date of Information",
"public".fieldreport.reportreceiveddate AS "Data Entry Date",
"public".fieldreport.reportcompleteddate AS "Submitted Date",
"public".fieldreport.reportverifieddate AS "Approved Date",
"public".fieldreport.verifiedby AS "Approved by",
"public".fieldreport.dataenterer AS "FR Data enterer",
imsmaenum.enumvalue
FROM
fieldreport
Inner Join imsmaenum ON (imsmaenum.imsmaenum_guid = fieldreport.workbenchstatusenum_guid)
WHERE
imsmaenum.enumvalue =  'Approved' AND
(fieldreport.reportverifieddate IS NULL  OR
fieldreport.verifiedby IS NULL )
ORDER BY
"Form ID" ASC

If you have any output here you need to take action. The queries below help you to identify for which item(s) the rows in the table fieldreport need to be updated.

Accident

SELECT
"public".fieldreport.fieldreport_localid AS "Form ID",
"public".fieldreport.dateofreport AS "Date of Information",
"public".fieldreport.reportreceiveddate AS "Data Entry Date",
"public".fieldreport.reportcompleteddate AS "Submitted Date",
"public".fieldreport.reportverifieddate AS "Approved Date",
"public".fieldreport.verifiedby AS "Approved by",
"public".imsmaenum.enumvalue,
"public".fieldreport.dataentrydate AS "FR Data date",
"public".fieldreport.dataenterer AS "FR Data enterer",
"public".accidentinfoversion.dataentrydate AS "InfoDate",
"public".accidentinfoversion.dataenterer AS "InfoEnterer"
FROM
fieldreport
INNER JOIN imsmaenum ON imsmaenum.imsmaenum_guid = fieldreport.workbenchstatusenum_guid
INNER JOIN accidentinfoversion ON accidentinfoversion.fieldreport_guid = fieldreport.fieldreport_guid
WHERE
imsmaenum.enumvalue = 'Approved' AND
(fieldreport.reportverifieddate IS NULL OR
fieldreport.verifiedby IS NULL) AND
accidentinfoversion.link_only = 'f'
ORDER BY
"Date of Information" ASC

Hazard

SELECT
"public".fieldreport.fieldreport_localid AS "Form ID",
"public".fieldreport.dateofreport AS "Date of Information",
"public".fieldreport.reportreceiveddate AS "Data Entry Date",
"public".fieldreport.reportcompleteddate AS "Submitted Date",
"public".fieldreport.reportverifieddate AS "Approved Date",
"public".fieldreport.verifiedby AS "Approved by",
"public".imsmaenum.enumvalue,
"public".fieldreport.dataentrydate AS "FR Data date",
"public".fieldreport.dataenterer AS "FR Data enterer",
"public".hazardinfoversion.dataentrydate AS "InfoDate",
"public".hazardinfoversion.dataenterer AS "InfoEnterer"
FROM
fieldreport
INNER JOIN imsmaenum  ON imsmaenum.imsmaenum_guid = fieldreport.workbenchstatusenum_guid
INNER JOIN hazardinfoversion ON fieldreport.fieldreport_guid = hazardinfoversion.fieldreport_guid
WHERE
imsmaenum.enumvalue = 'Approved' AND
(fieldreport.reportverifieddate IS NULL OR
fieldreport.verifiedby IS NULL) AND
hazardinfoversion.link_only = 'f'
ORDER BY
"Date of Information" ASC

Hazard Reducation

SELECT
"public".fieldreport.fieldreport_localid AS "Form ID",
"public".fieldreport.dateofreport AS "Date of Information",
"public".fieldreport.reportreceiveddate AS "Data Entry Date",
"public".fieldreport.reportcompleteddate AS "Submitted Date",
"public".fieldreport.reportverifieddate AS "Approved Date",
"public".fieldreport.verifiedby AS "Approved by",
"public".imsmaenum.enumvalue,
"public".fieldreport.dataentrydate AS "FR Data date",
"public".fieldreport.dataenterer AS "FR Data enterer",
"public".hazreducinfoversion.dataentrydate AS "InfoDate",
"public".hazreducinfoversion.dataenterer AS "InfoEnterer"
FROM
fieldreport
INNER JOIN imsmaenum  ON imsmaenum.imsmaenum_guid = fieldreport.workbenchstatusenum_guid
INNER JOIN hazreducinfoversion ON fieldreport.fieldreport_guid = hazreducinfoversion.fieldreport_guid
WHERE
imsmaenum.enumvalue = 'Approved' AND
(fieldreport.reportverifieddate IS NULL OR
fieldreport.verifiedby IS NULL) AND
hazreducinfoversion.link_only = 'f'
ORDER BY
"Date of Information" ASC

Location

SELECT
"public".fieldreport.fieldreport_localid AS "Form ID",
"public".fieldreport.dateofreport AS "Date of Information",
"public".fieldreport.reportreceiveddate AS "Data Entry Date",
"public".fieldreport.reportcompleteddate AS "Submitted Date",
"public".fieldreport.reportverifieddate AS "Approved Date",
"public".fieldreport.verifiedby AS "Approved by",
"public".imsmaenum.enumvalue,
"public".fieldreport.dataentrydate AS "FR Data date",
"public".fieldreport.dataenterer AS "FR Data enterer",
"public".locationinfoversion.dataentrydate AS "InfoDate",
"public".locationinfoversion.dataenterer AS "InfoEnterer"
FROM
fieldreport
INNER JOIN imsmaenum  ON imsmaenum.imsmaenum_guid = fieldreport.workbenchstatusenum_guid
INNER JOIN locationinfoversion ON fieldreport.fieldreport_guid = locationinfoversion.fieldreport_guid
WHERE
imsmaenum.enumvalue = 'Approved' AND
(fieldreport.reportverifieddate IS NULL OR
fieldreport.verifiedby IS NULL) AND
locationinfoversion.link_only = 'f'
ORDER BY
"Date of Information" ASC

MRE

SELECT
"public".fieldreport.fieldreport_localid AS "Form ID",
"public".fieldreport.dateofreport AS "Date of Information",
"public".fieldreport.reportreceiveddate AS "Data Entry Date",
"public".fieldreport.reportcompleteddate AS "Submitted Date",
"public".fieldreport.reportverifieddate AS "Approved Date",
"public".fieldreport.verifiedby AS "Approved by",
"public".imsmaenum.enumvalue,
"public".fieldreport.dataentrydate AS "FR Data date",
"public".fieldreport.dataenterer AS "FR Data enterer",
"public".mreinfoversion.dataentrydate AS "InfoDate",
"public".mreinfoversion.dataenterer AS "InfoEnterer"
FROM
fieldreport
INNER JOIN imsmaenum  ON imsmaenum.imsmaenum_guid = fieldreport.workbenchstatusenum_guid
INNER JOIN mreinfoversion ON fieldreport.fieldreport_guid = mreinfoversion.fieldreport_guid
WHERE
imsmaenum.enumvalue = 'Approved' AND
(fieldreport.reportverifieddate IS NULL OR
fieldreport.verifiedby IS NULL) AND
mreinfoversion.link_only = 'f'
ORDER BY
"Date of Information" ASC

QM

SELECT
"public".fieldreport.fieldreport_localid AS "Form ID",
"public".fieldreport.dateofreport AS "Date of Information",
"public".fieldreport.reportreceiveddate AS "Data Entry Date",
"public".fieldreport.reportcompleteddate AS "Submitted Date",
"public".fieldreport.reportverifieddate AS "Approved Date",
"public".fieldreport.verifiedby AS "Approved by",
"public".imsmaenum.enumvalue,
"public".fieldreport.dataentrydate AS "FR Data date",
"public".fieldreport.dataenterer AS "FR Data enterer",
"public".qainfoversion.dataentrydate AS "InfoDate",
"public".qainfoversion.dataenterer AS "InfoEnterer"
FROM
fieldreport
INNER JOIN imsmaenum  ON imsmaenum.imsmaenum_guid = fieldreport.workbenchstatusenum_guid
INNER JOIN qainfoversion ON fieldreport.fieldreport_guid = qainfoversion.fieldreport_guid
WHERE
imsmaenum.enumvalue = 'Approved' AND
(fieldreport.reportverifieddate IS NULL OR
fieldreport.verifiedby IS NULL) AND
qainfoversion.link_only = 'f'
ORDER BY
"Date of Information" ASC

Victim

SELECT
"public".fieldreport.fieldreport_localid AS "Form ID",
"public".fieldreport.dateofreport AS "Date of Information",
"public".fieldreport.reportreceiveddate AS "Data Entry Date",
"public".fieldreport.reportcompleteddate AS "Submitted Date",
"public".fieldreport.reportverifieddate AS "Approved Date",
"public".fieldreport.verifiedby AS "Approved by",
"public".imsmaenum.enumvalue,
"public".fieldreport.dataentrydate AS "FR Data date",
"public".fieldreport.dataenterer AS "FR Data enterer",
"public".victiminfoversion.dataentrydate AS "InfoDate",
"public".victiminfoversion.dataenterer AS "InfoEnterer"
FROM
fieldreport
INNER JOIN imsmaenum  ON imsmaenum.imsmaenum_guid = fieldreport.workbenchstatusenum_guid
INNER JOIN victiminfoversion ON fieldreport.fieldreport_guid = victiminfoversion.fieldreport_guid
WHERE
imsmaenum.enumvalue = 'Approved' AND
(fieldreport.reportverifieddate IS NULL OR
fieldreport.verifiedby IS NULL) AND
victiminfoversion.link_only = 'f'
ORDER BY
"Date of Information" ASC

Example update query

UPDATE fieldreport
SET
reportverifieddate = hazreducinfoversion.dataentrydate
FROM imsmaenum, hazreducinfoversion
WHERE
imsmaenum.imsmaenum_guid = fieldreport.workbenchstatusenum_guid AND
hazreducinfoversion.fieldreport_guid = fieldreport.fieldreport_guid AND
imsmaenum.enumvalue = 'Approved' AND
fieldreport.reportverifieddate IS NULL AND
hazreducinfoversion.link_only = 'f' AND
dateofreport is not NULL AND
dateofreport < hazreducinfoversion.dataentrydate

UPDATE fieldreport
SET
reportverifieddate = dateofreport
FROM imsmaenum, hazreducinfoversion
WHERE
imsmaenum.imsmaenum_guid = fieldreport.workbenchstatusenum_guid AND
hazreducinfoversion.fieldreport_guid = fieldreport.fieldreport_guid AND
imsmaenum.enumvalue = 'Approved' AND
fieldreport.reportverifieddate IS NULL AND
hazreducinfoversion.link_only = 'f' AND
dateofreport is not NULL AND
dateofreport > hazreducinfoversion.dataentrydate

update fieldreport
SET
verifiedby = fieldreport.dataenterer
FROM imsmaenum, hazreducinfoversion
WHERE
imsmaenum.imsmaenum_guid = fieldreport.workbenchstatusenum_guid AND
hazreducinfoversion.fieldreport_guid = fieldreport.fieldreport_guid AND
imsmaenum.enumvalue =  'Approved' AND
fieldreport.verifiedby IS NULL  AND
hazreducinfoversion.link_only =  'f' and 
fieldreport.dataenterer is not null

Template:NavBox Hub

Retrieved from "http://mwiki.gichd.org/IM/index.php?title=Missing_approval_information_PGSQL&oldid=22480"