Missing Date of Information PGSQL: Difference between revisions

From IMSMA Wiki
Jump to navigation Jump to search
← Older edit
No edit summary
No edit summary
 
Line 1: Line 1:
{{Under construction| The SQL below are in MySQL.}}
{{TOC right}}
{{TOC right}}
{{Note | The Summary item/window calculation is '''depending''' on that ''Date of Information'' is filled in and the rules for if a DEF is Unapprovable are also depending on ''Date of Information''.}}
{{Note | The Summary item/window calculation is '''depending''' on that ''Date of Information'' is filled in and the rules for if a DEF is Unapprovable are also depending on ''Date of Information''.
 
There is a MySQL version of this page '''[[Missing Date of Information| too]]'''.}}


==Missing Date of Information ==__NOEDITSECTION__  
==Missing Date of Information ==__NOEDITSECTION__  
Line 11: Line 12:
<pre>
<pre>
SELECT
SELECT
fieldreport.fieldreport_localid AS `Form ID`,
"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.dataenterer AS "Data enterer",
fieldreport.verifiedby AS `Approved by`,
"public".fieldreport.verifiedby AS "Approved by",
imsmaenum.enumvalue AS `WB status`
"public".imsmaenum.enumvalue AS "WB status"
FROM
FROM
fieldreport
"public".fieldreport
INNER JOIN imsmaenum ON fieldreport.workbenchstatusenum_guid = imsmaenum.imsmaenum_guid
INNER JOIN "public".imsmaenum ON "public".fieldreport.workbenchstatusenum_guid = "public".imsmaenum.imsmaenum_guid
WHERE
WHERE
fieldreport.dateofreport IS NULL  
"public".fieldreport.dateofreport IS NULL  
ORDER BY
ORDER BY
imsmaenum.enumvalue DESC, `Form ID` ASC
"WB status" DESC,
"Form ID" ASC
</pre>
</pre>


Line 38: Line 40:
<pre>
<pre>
SELECT
SELECT
fieldreport.fieldreport_localid AS `Form ID`,
"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".imsmaenum.enumvalue AS "WB status",
imsmaenum.enumvalue AS `WB status`,
"public".hazardinfoversion.dataentrydate AS "InfoVerDate"
hazardinfoversion.dataentrydate AS InfoVerDate
FROM
FROM
fieldreport
"public".fieldreport
INNER JOIN hazardinfoversion ON fieldreport.fieldreport_guid = hazardinfoversion.fieldreport_guid
INNER JOIN "public".hazardinfoversion ON "public".fieldreport.fieldreport_guid = "public".hazardinfoversion.fieldreport_guid
INNER JOIN imsmaenum ON fieldreport.workbenchstatusenum_guid = imsmaenum.imsmaenum_guid
INNER JOIN "public".imsmaenum ON "public".fieldreport.workbenchstatusenum_guid = "public".imsmaenum.imsmaenum_guid
WHERE
WHERE
fieldreport.dateofreport IS NULL AND
"public".fieldreport.dateofreport IS NULL AND
hazardinfoversion.link_only = '0'
"public".hazardinfoversion.link_only = 'f'
ORDER BY
ORDER BY
InfoVerDate ASC,
"InfoVerDate" ASC,
`Form ID` ASC
"Form ID" ASC
</pre>
</pre>


Line 63: Line 64:
<pre>
<pre>
SELECT
SELECT
fieldreport.fieldreport_localid AS `Form ID`,
"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".imsmaenum.enumvalue AS "WB status",
imsmaenum.enumvalue AS `WB status`,
"public".accidentinfoversion.dataentrydate AS "InfoVerDate"
accidentinfoversion.dataentrydate AS InfoVerDate
FROM
FROM
fieldreport
"public".fieldreport
Inner Join accidentinfoversion ON fieldreport.fieldreport_guid = accidentinfoversion.fieldreport_guid
INNER JOIN "public".accidentinfoversion ON "public".fieldreport.fieldreport_guid = "public".accidentinfoversion.fieldreport_guid
INNER JOIN imsmaenum ON fieldreport.workbenchstatusenum_guid = imsmaenum.imsmaenum_guid
INNER JOIN "public".imsmaenum ON "public".fieldreport.workbenchstatusenum_guid = "public".imsmaenum.imsmaenum_guid
WHERE
WHERE
fieldreport.dateofreport IS NULL AND
"public".fieldreport.dateofreport IS NULL AND
accidentinfoversion.link_only = '0'
"public".accidentinfoversion.link_only = 'f'  
ORDER BY
ORDER BY
InfoVerDate ASC,
"InfoVerDate" ASC,
`Form ID` ASC
"Form ID" ASC
</pre>
</pre>


Line 88: Line 88:
<pre>
<pre>
SELECT
SELECT
fieldreport.fieldreport_localid AS `Form ID`,
"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".imsmaenum.enumvalue AS "WB status",
hazreducinfoversion.dataentrydate AS InfoVerDate,  
"public".hazreducinfoversion.dataentrydate AS "InfoVerDate"
hazreducinfoversion.enddate
FROM
FROM
fieldreport
"public".fieldreport
Inner Join hazreducinfoversion ON fieldreport.fieldreport_guid = hazreducinfoversion.fieldreport_guid
INNER JOIN "public".hazreducinfoversion ON "public".fieldreport.fieldreport_guid = "public".hazreducinfoversion.fieldreport_guid
INNER JOIN imsmaenum ON fieldreport.workbenchstatusenum_guid = imsmaenum.imsmaenum_guid
INNER JOIN "public".imsmaenum ON "public".fieldreport.workbenchstatusenum_guid = "public".imsmaenum.imsmaenum_guid
WHERE
WHERE
fieldreport.dateofreport IS NULL AND
"public".fieldreport.dateofreport IS NULL AND
hazreducinfoversion.link_only = '0'
"public".hazreducinfoversion.link_only = 'f'  
ORDER BY
ORDER BY
InfoVerDate ASC,
"InfoVerDate" ASC,
`Form ID` ASC
"Form ID" ASC
</pre>
</pre>


Line 113: Line 112:
<pre>
<pre>
SELECT
SELECT
fieldreport.fieldreport_localid AS `Form ID`,
"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".imsmaenum.enumvalue AS "WB status",
imsmaenum.enumvalue AS `WB status`,
"public".locationinfoversion.dataentrydate AS "InfoVerDate"
locationinfoversion.dataentrydate AS InfoVerDate
FROM
FROM
fieldreport
"public".fieldreport
Inner Join locationinfoversion ON fieldreport.fieldreport_guid = locationinfoversion.fieldreport_guid
INNER JOIN "public".locationinfoversion ON "public".fieldreport.fieldreport_guid = "public".locationinfoversion.fieldreport_guid
INNER JOIN imsmaenum ON fieldreport.workbenchstatusenum_guid = imsmaenum.imsmaenum_guid
INNER JOIN "public".imsmaenum ON "public".fieldreport.workbenchstatusenum_guid = "public".imsmaenum.imsmaenum_guid
WHERE
WHERE
fieldreport.dateofreport IS NULL AND
"public".fieldreport.dateofreport IS NULL AND
locationinfoversion.link_only = '0'
"public".locationinfoversion.link_only = 'f'  
ORDER BY
ORDER BY
InfoVerDate ASC,
"InfoVerDate" ASC,
`Form ID` ASC
"Form ID" ASC
</pre>
</pre>


Line 138: Line 136:
<pre>
<pre>
SELECT
SELECT
fieldreport.fieldreport_localid AS `Form ID`,
"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".imsmaenum.enumvalue AS "WB status",
imsmaenum.enumvalue AS `WB status`,
"public".mreinfoversion.dataentrydate AS "InfoVerDate"
mreinfoversion.dataentrydate AS InfoVerDate
FROM
FROM
fieldreport
"public".fieldreport
Inner Join mreinfoversion ON fieldreport.fieldreport_guid = mreinfoversion.fieldreport_guid
INNER JOIN "public".mreinfoversion ON "public".fieldreport.fieldreport_guid = "public".mreinfoversion.fieldreport_guid
INNER JOIN imsmaenum ON fieldreport.workbenchstatusenum_guid = imsmaenum.imsmaenum_guid
INNER JOIN "public".imsmaenum ON "public".fieldreport.workbenchstatusenum_guid = "public".imsmaenum.imsmaenum_guid
WHERE
WHERE
fieldreport.dateofreport IS NULL AND
"public".fieldreport.dateofreport IS NULL AND
mreinfoversion.link_only = '0'
"public".mreinfoversion.link_only = 'f'  
ORDER BY
ORDER BY
InfoVerDate ASC,
"InfoVerDate" ASC,
`Form ID` ASC
"Form ID" ASC
</pre>
</pre>


Line 163: Line 160:
<pre>
<pre>
SELECT
SELECT
fieldreport.fieldreport_localid AS `Form ID`,
"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".imsmaenum.enumvalue AS "WB status",
imsmaenum.enumvalue AS `WB status`,
"public".qainfoversion.dataentrydate AS "InfoVerDate"
qainfoversion.dataentrydate AS InfoVerDate
FROM
FROM
fieldreport
"public".fieldreport
Inner Join qainfoversion ON fieldreport.fieldreport_guid = qainfoversion.fieldreport_guid
INNER JOIN "public".qainfoversion ON "public".fieldreport.fieldreport_guid = "public".qainfoversion.fieldreport_guid
INNER JOIN imsmaenum ON fieldreport.workbenchstatusenum_guid = imsmaenum.imsmaenum_guid
INNER JOIN "public".imsmaenum ON "public".fieldreport.workbenchstatusenum_guid = "public".imsmaenum.imsmaenum_guid
WHERE
WHERE
fieldreport.dateofreport IS NULL AND
"public".fieldreport.dateofreport IS NULL AND
qainfoversion.link_only = '0'
"public".qainfoversion.link_only = 'f'  
ORDER BY
ORDER BY
InfoVerDate ASC,
"InfoVerDate" ASC,
`Form ID` ASC
"Form ID" ASC
</pre>
</pre>


Line 188: Line 184:
<pre>
<pre>
SELECT
SELECT
fieldreport.fieldreport_localid AS `Form ID`,
"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".imsmaenum.enumvalue AS "WB status",
imsmaenum.enumvalue AS `WB status`,
"public".victiminfoversion.dataentrydate AS "InfoVerDate"
victiminfoversion.dataentrydate AS InfoVerDate
FROM
FROM
fieldreport
"public".fieldreport
Inner Join victiminfoversion ON fieldreport.fieldreport_guid = victiminfoversion.fieldreport_guid
INNER JOIN "public".victiminfoversion ON "public".fieldreport.fieldreport_guid = "public".victiminfoversion.fieldreport_guid
INNER JOIN imsmaenum ON fieldreport.workbenchstatusenum_guid = imsmaenum.imsmaenum_guid
INNER JOIN "public".imsmaenum ON "public".fieldreport.workbenchstatusenum_guid = "public".imsmaenum.imsmaenum_guid
WHERE
WHERE
fieldreport.dateofreport IS NULL AND
"public".fieldreport.dateofreport IS NULL AND
victiminfoversion.link_only = '0'
"public".victiminfoversion.link_only = 'f'  
ORDER BY
ORDER BY
InfoVerDate ASC,
"InfoVerDate" ASC,
`Form ID` ASC
"Form ID" ASC
</pre>
</pre>


Line 212: Line 207:
<pre>
<pre>
update fieldreport
update fieldreport
Inner Join hazardinfoversion ON fieldreport.fieldreport_guid = hazardinfoversion.fieldreport_guid
SET
SET
fieldreport.dateofreport = hazardinfoversion.dataentrydate
dateofreport = hazardinfoversion.dataentrydate
FROM imsmaenum, hazardinfoversion
WHERE
WHERE
fieldreport.dateofreport IS NULL
imsmaenum.imsmaenum_guid = fieldreport.workbenchstatusenum_guid AND
AND
fieldreport.fieldreport_guid = hazardinfoversion.fieldreport_guid AND
hazardinfoversion.link_only =  '0' AND
fieldreport.dateofreport IS NULL AND
hazardinfoversion.dataentrydate IS NOT NULL
hazardinfoversion.link_only =  'f' AND
hazardinfoversion.dataentrydate < fieldreport.reportreceiveddate
</pre>
</pre>


Latest revision as of 06:33, 11 November 2016

The Summary item/window calculation is depending on that Date of Information is filled in and the rules for if a DEF is Unapprovable are also depending on Date of Information.

There is a MySQL version of this page too.

Missing Date of Information

How it should NOT look like

If Date of Information is missing it has to be set. If the DEF that misses Date of Information is an Activity or Education then you might be able to use End date. 

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.dataenterer AS "Data enterer",
"public".fieldreport.verifiedby AS "Approved by",
"public".imsmaenum.enumvalue AS "WB status"
FROM
"public".fieldreport
INNER JOIN "public".imsmaenum ON "public".fieldreport.workbenchstatusenum_guid = "public".imsmaenum.imsmaenum_guid
WHERE
"public".fieldreport.dateofreport IS NULL 
ORDER BY
"WB status" DESC,
"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.

Hazard

Example of result - it is important to decide where you may find a date to use that is in chronological order.

With this result set you can show the country which Hazards are affected and figure out a solution. 

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 AS "WB status",
"public".hazardinfoversion.dataentrydate AS "InfoVerDate"
FROM
"public".fieldreport
INNER JOIN "public".hazardinfoversion ON "public".fieldreport.fieldreport_guid = "public".hazardinfoversion.fieldreport_guid
INNER JOIN "public".imsmaenum ON "public".fieldreport.workbenchstatusenum_guid = "public".imsmaenum.imsmaenum_guid
WHERE
"public".fieldreport.dateofreport IS NULL AND
"public".hazardinfoversion.link_only = 'f'
ORDER BY
"InfoVerDate" ASC,
"Form ID" ASC

Accident

With this result set you can show the country which Accidents are affected and figure out a solution. 

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 AS "WB status",
"public".accidentinfoversion.dataentrydate AS "InfoVerDate"
FROM
"public".fieldreport
INNER JOIN "public".accidentinfoversion ON "public".fieldreport.fieldreport_guid = "public".accidentinfoversion.fieldreport_guid
INNER JOIN "public".imsmaenum ON "public".fieldreport.workbenchstatusenum_guid = "public".imsmaenum.imsmaenum_guid
WHERE
"public".fieldreport.dateofreport IS NULL AND
"public".accidentinfoversion.link_only = 'f' 
ORDER BY
"InfoVerDate" ASC,
"Form ID" ASC

Hazard reduction

With this result set you can show the country which Hazard Reductions are affected and figure out a solution e.g. use End date if NOT NULL 

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 AS "WB status",
"public".hazreducinfoversion.dataentrydate AS "InfoVerDate"
FROM
"public".fieldreport
INNER JOIN "public".hazreducinfoversion ON "public".fieldreport.fieldreport_guid = "public".hazreducinfoversion.fieldreport_guid
INNER JOIN "public".imsmaenum ON "public".fieldreport.workbenchstatusenum_guid = "public".imsmaenum.imsmaenum_guid
WHERE
"public".fieldreport.dateofreport IS NULL AND
"public".hazreducinfoversion.link_only = 'f' 
ORDER BY
"InfoVerDate" ASC,
"Form ID" ASC

Location

With this result set you can show the country which Loctions are affected and figure out a solution. 

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 AS "WB status",
"public".locationinfoversion.dataentrydate  AS "InfoVerDate"
FROM
"public".fieldreport
INNER JOIN "public".locationinfoversion ON "public".fieldreport.fieldreport_guid = "public".locationinfoversion.fieldreport_guid
INNER JOIN "public".imsmaenum ON "public".fieldreport.workbenchstatusenum_guid = "public".imsmaenum.imsmaenum_guid
WHERE
"public".fieldreport.dateofreport IS NULL AND
"public".locationinfoversion.link_only = 'f' 
ORDER BY
"InfoVerDate" ASC,
"Form ID" ASC

MRE

With this result set you can show the country which MREs are affected and figure out a solution. 

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 AS "WB status",
"public".mreinfoversion.dataentrydate AS "InfoVerDate"
FROM
"public".fieldreport
INNER JOIN "public".mreinfoversion ON "public".fieldreport.fieldreport_guid = "public".mreinfoversion.fieldreport_guid
INNER JOIN "public".imsmaenum ON "public".fieldreport.workbenchstatusenum_guid = "public".imsmaenum.imsmaenum_guid
WHERE
"public".fieldreport.dateofreport IS NULL AND
"public".mreinfoversion.link_only = 'f' 
ORDER BY
"InfoVerDate" ASC,
"Form ID" ASC

QM

With this result set you can show the country which QMs are affected and figure out a solution. 

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 AS "WB status",
"public".qainfoversion.dataentrydate AS "InfoVerDate"
FROM
"public".fieldreport
INNER JOIN "public".qainfoversion ON "public".fieldreport.fieldreport_guid = "public".qainfoversion.fieldreport_guid
INNER JOIN "public".imsmaenum ON "public".fieldreport.workbenchstatusenum_guid = "public".imsmaenum.imsmaenum_guid
WHERE
"public".fieldreport.dateofreport IS NULL AND
"public".qainfoversion.link_only = 'f' 
ORDER BY
"InfoVerDate" ASC,
"Form ID" ASC

Victim

With this result set you can show the country which Victims are affected and figure out a solution. 

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 AS "WB status",
"public".victiminfoversion.dataentrydate AS "InfoVerDate"
FROM
"public".fieldreport
INNER JOIN "public".victiminfoversion ON "public".fieldreport.fieldreport_guid = "public".victiminfoversion.fieldreport_guid
INNER JOIN "public".imsmaenum ON "public".fieldreport.workbenchstatusenum_guid = "public".imsmaenum.imsmaenum_guid
WHERE
"public".fieldreport.dateofreport IS NULL AND
"public".victiminfoversion.link_only = 'f' 
ORDER BY
"InfoVerDate" ASC,
"Form ID" ASC

Example UPDATE query

update fieldreport
SET
dateofreport = hazardinfoversion.dataentrydate
FROM imsmaenum, hazardinfoversion
WHERE
imsmaenum.imsmaenum_guid = fieldreport.workbenchstatusenum_guid AND
fieldreport.fieldreport_guid = hazardinfoversion.fieldreport_guid AND
fieldreport.dateofreport IS NULL  AND
hazardinfoversion.link_only =  'f' AND
hazardinfoversion.dataentrydate < fieldreport.reportreceiveddate

Future date

Date of Information in the future. Use one day after the backup was taken in the WHERE. 

SELECT
fieldreport.fieldreport_localid,
fieldreport.dateofreport
FROM
fieldreport
WHERE
fieldreport.dateofreport >  '2015-04-07'
Order by 2

Template:NavBox Hub

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