NoPublic, bureaucrat, administrator
6,632
edits
Changes
Created page with "{{Under construction| The SQL below are in MySQL.}} {{TOC right}} {{Note | The Summary item/window calculation is '''depending''' on that ''Date of Information'' is filled in..."
{{Under construction| The SQL below are in MySQL.}}
{{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''.}}
==Missing Date of Information ==__NOEDITSECTION__
[[Image:Missing date info.png|1000px|center]]
<div align="center">
'' How it should NOT look like''
</div>
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''.
<pre>
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
</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.
=== Hazard===__NOEDITSECTION__
[[Image:Missing date info2.png|1000px|center]]
<div align="center">
'' Example of result - it is '''important''' to decide where you may find a date to use that is in chronological order. ''
</div>
With this result set you can show the country which Hazards are affected and figure out a solution.
<pre>
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
</pre>
=== Accident===__NOEDITSECTION__
With this result set you can show the country which Accidents are affected and figure out a solution.
<pre>
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
</pre>
=== Hazard reduction===__NOEDITSECTION__
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
<pre>
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
</pre>
=== Location===__NOEDITSECTION__
With this result set you can show the country which Loctions are affected and figure out a solution.
<pre>
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
</pre>
=== MRE===__NOEDITSECTION__
With this result set you can show the country which MREs are affected and figure out a solution.
<pre>
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
</pre>
=== QM===__NOEDITSECTION__
With this result set you can show the country which QMs are affected and figure out a solution.
<pre>
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
</pre>
=== Victim===__NOEDITSECTION__
With this result set you can show the country which Victims are affected and figure out a solution.
<pre>
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
</pre>
==Example UPDATE query==__NOEDITSECTION__
<pre>
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
</pre>
==Future date==__NOEDITSECTION__
Date of Information in the '''future'''. Use one day after the backup was taken in the WHERE.
<pre>
SELECT
fieldreport.fieldreport_localid,
fieldreport.dateofreport
FROM
fieldreport
WHERE
fieldreport.dateofreport > '2015-04-07'
Order by 2
</pre>
{{NavBox Upgrade}}
[[Category:NoPublic]]
[[Category:SQL Queries]]
{{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''.}}
==Missing Date of Information ==__NOEDITSECTION__
[[Image:Missing date info.png|1000px|center]]
<div align="center">
'' How it should NOT look like''
</div>
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''.
<pre>
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
</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.
=== Hazard===__NOEDITSECTION__
[[Image:Missing date info2.png|1000px|center]]
<div align="center">
'' Example of result - it is '''important''' to decide where you may find a date to use that is in chronological order. ''
</div>
With this result set you can show the country which Hazards are affected and figure out a solution.
<pre>
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
</pre>
=== Accident===__NOEDITSECTION__
With this result set you can show the country which Accidents are affected and figure out a solution.
<pre>
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
</pre>
=== Hazard reduction===__NOEDITSECTION__
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
<pre>
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
</pre>
=== Location===__NOEDITSECTION__
With this result set you can show the country which Loctions are affected and figure out a solution.
<pre>
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
</pre>
=== MRE===__NOEDITSECTION__
With this result set you can show the country which MREs are affected and figure out a solution.
<pre>
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
</pre>
=== QM===__NOEDITSECTION__
With this result set you can show the country which QMs are affected and figure out a solution.
<pre>
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
</pre>
=== Victim===__NOEDITSECTION__
With this result set you can show the country which Victims are affected and figure out a solution.
<pre>
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
</pre>
==Example UPDATE query==__NOEDITSECTION__
<pre>
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
</pre>
==Future date==__NOEDITSECTION__
Date of Information in the '''future'''. Use one day after the backup was taken in the WHERE.
<pre>
SELECT
fieldreport.fieldreport_localid,
fieldreport.dateofreport
FROM
fieldreport
WHERE
fieldreport.dateofreport > '2015-04-07'
Order by 2
</pre>
{{NavBox Upgrade}}
[[Category:NoPublic]]
[[Category:SQL Queries]]
