Missing Date of Information PGSQL

From IMSMA Wiki
Jump to navigation Jump to search
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"