Missing Date of Information PGSQL

From IMSMA Wiki
Revision as of 17:22, 10 November 2016 by Alnaucler (talk | contribs)
Jump to navigation Jump to search
The SQL below are in MySQL.
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.

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
fieldreport.fieldreport_localid AS `Form ID`,
fieldreport.dateofreport AS `Date of Information`,
fieldreport.reportreceiveddate AS `Data Entry Date`,
fieldreport.reportcompleteddate AS `Submitted Date`,
fieldreport.reportverifieddate AS `Approved Date`,
fieldreport.dataenterer AS `Data enterer`,
fieldreport.verifiedby AS `Approved by`,
imsmaenum.enumvalue AS `WB status`
FROM
fieldreport
INNER JOIN imsmaenum ON fieldreport.workbenchstatusenum_guid = imsmaenum.imsmaenum_guid
WHERE
fieldreport.dateofreport IS NULL 
ORDER BY
imsmaenum.enumvalue 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
fieldreport.fieldreport_localid AS `Form ID`,
fieldreport.dateofreport AS `Date of Information`,
fieldreport.reportreceiveddate AS `Data Entry Date`,
fieldreport.reportcompleteddate AS `Submitted Date`,
fieldreport.reportverifieddate AS `Approved Date`,
fieldreport.dataenterer AS `Data enterer`,
fieldreport.verifiedby AS `Approved by`,
imsmaenum.enumvalue AS `WB status`,
hazardinfoversion.dataentrydate AS InfoVerDate
FROM
fieldreport
INNER JOIN hazardinfoversion ON fieldreport.fieldreport_guid = hazardinfoversion.fieldreport_guid
INNER JOIN imsmaenum ON fieldreport.workbenchstatusenum_guid = imsmaenum.imsmaenum_guid
WHERE
fieldreport.dateofreport IS NULL  AND
hazardinfoversion.link_only =  '0'
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
fieldreport.fieldreport_localid AS `Form ID`,
fieldreport.dateofreport AS `Date of Information`,
fieldreport.reportreceiveddate AS `Data Entry Date`,
fieldreport.reportcompleteddate AS `Submitted Date`,
fieldreport.reportverifieddate AS `Approved Date`,
fieldreport.dataenterer AS `Data enterer`,
fieldreport.verifiedby AS `Approved by`,
imsmaenum.enumvalue AS `WB status`,
accidentinfoversion.dataentrydate AS InfoVerDate
FROM
fieldreport
Inner Join accidentinfoversion ON fieldreport.fieldreport_guid = accidentinfoversion.fieldreport_guid
INNER JOIN imsmaenum ON fieldreport.workbenchstatusenum_guid = imsmaenum.imsmaenum_guid
WHERE
fieldreport.dateofreport IS NULL  AND
accidentinfoversion.link_only =  '0'
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
fieldreport.fieldreport_localid AS `Form ID`,
fieldreport.dateofreport AS `Date of Information`,
fieldreport.reportreceiveddate AS `Data Entry Date`,
fieldreport.reportcompleteddate AS `Submitted Date`,
fieldreport.reportverifieddate AS `Approved Date`,
fieldreport.dataenterer AS `Data enterer`,
fieldreport.verifiedby AS `Approved by`,
hazreducinfoversion.dataentrydate AS InfoVerDate, 
hazreducinfoversion.enddate
FROM
fieldreport
Inner Join hazreducinfoversion ON fieldreport.fieldreport_guid = hazreducinfoversion.fieldreport_guid
INNER JOIN imsmaenum ON fieldreport.workbenchstatusenum_guid = imsmaenum.imsmaenum_guid
WHERE
fieldreport.dateofreport IS NULL  AND
hazreducinfoversion.link_only =  '0'
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
fieldreport.fieldreport_localid AS `Form ID`,
fieldreport.dateofreport AS `Date of Information`,
fieldreport.reportreceiveddate AS `Data Entry Date`,
fieldreport.reportcompleteddate AS `Submitted Date`,
fieldreport.reportverifieddate AS `Approved Date`,
fieldreport.dataenterer AS `Data enterer`,
fieldreport.verifiedby AS `Approved by`,
imsmaenum.enumvalue AS `WB status`,
locationinfoversion.dataentrydate AS InfoVerDate
FROM
fieldreport
Inner Join locationinfoversion ON fieldreport.fieldreport_guid = locationinfoversion.fieldreport_guid
INNER JOIN imsmaenum ON fieldreport.workbenchstatusenum_guid = imsmaenum.imsmaenum_guid
WHERE
fieldreport.dateofreport IS NULL  AND
locationinfoversion.link_only =  '0'
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
fieldreport.fieldreport_localid AS `Form ID`,
fieldreport.dateofreport AS `Date of Information`,
fieldreport.reportreceiveddate AS `Data Entry Date`,
fieldreport.reportcompleteddate AS `Submitted Date`,
fieldreport.reportverifieddate AS `Approved Date`,
fieldreport.dataenterer AS `Data enterer`,
fieldreport.verifiedby AS `Approved by`,
imsmaenum.enumvalue AS `WB status`,
mreinfoversion.dataentrydate  AS InfoVerDate
FROM
fieldreport
Inner Join mreinfoversion ON fieldreport.fieldreport_guid = mreinfoversion.fieldreport_guid
INNER JOIN imsmaenum ON fieldreport.workbenchstatusenum_guid = imsmaenum.imsmaenum_guid
WHERE
fieldreport.dateofreport IS NULL  AND
mreinfoversion.link_only =  '0'
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
fieldreport.fieldreport_localid AS `Form ID`,
fieldreport.dateofreport AS `Date of Information`,
fieldreport.reportreceiveddate AS `Data Entry Date`,
fieldreport.reportcompleteddate AS `Submitted Date`,
fieldreport.reportverifieddate AS `Approved Date`,
fieldreport.dataenterer AS `Data enterer`,
fieldreport.verifiedby AS `Approved by`,
imsmaenum.enumvalue AS `WB status`,
qainfoversion.dataentrydate AS InfoVerDate
FROM
fieldreport
Inner Join qainfoversion ON fieldreport.fieldreport_guid = qainfoversion.fieldreport_guid
INNER JOIN imsmaenum ON fieldreport.workbenchstatusenum_guid = imsmaenum.imsmaenum_guid
WHERE
fieldreport.dateofreport IS NULL  AND
qainfoversion.link_only =  '0'
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
fieldreport.fieldreport_localid AS `Form ID`,
fieldreport.dateofreport AS `Date of Information`,
fieldreport.reportreceiveddate AS `Data Entry Date`,
fieldreport.reportcompleteddate AS `Submitted Date`,
fieldreport.reportverifieddate AS `Approved Date`,
fieldreport.dataenterer AS `Data enterer`,
fieldreport.verifiedby AS `Approved by`,
imsmaenum.enumvalue AS `WB status`,
victiminfoversion.dataentrydate AS InfoVerDate
FROM
fieldreport
Inner Join victiminfoversion ON fieldreport.fieldreport_guid = victiminfoversion.fieldreport_guid
INNER JOIN imsmaenum ON fieldreport.workbenchstatusenum_guid = imsmaenum.imsmaenum_guid
WHERE
fieldreport.dateofreport IS NULL  AND
victiminfoversion.link_only =  '0'
ORDER BY
InfoVerDate ASC,
`Form ID` ASC

Example UPDATE query

update fieldreport
Inner Join hazardinfoversion ON fieldreport.fieldreport_guid = hazardinfoversion.fieldreport_guid
SET
fieldreport.dateofreport = hazardinfoversion.dataentrydate
WHERE
fieldreport.dateofreport IS NULL
AND
hazardinfoversion.link_only =  '0' AND
hazardinfoversion.dataentrydate IS NOT NULL

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=22418"