Check Link Table Approval State NEW - Revision history

Alnaucler at 11:49, 18 August 2017

2017-08-18T11:49:11Z

@@ Line 137: / Line 137: @@
 ORDER BY
 "public".victim.victim_localid ASC
 </pre>
 === Education ===__NOEDITSECTION__

Alnaucler at 20:11, 20 July 2017

2017-07-20T20:11:36Z

← Older revision		Revision as of 22:11, 20 July 2017
Line 230:		Line 230:

	=== UPDATE other than Location example ===__NOEDITSECTION__		=== UPDATE other than Location example ===__NOEDITSECTION__
	The difficulty is to how to identify which link row to transform to ''NEW''. I have tried Min(fieldreport.dateofreport) and Min(hazardinfoversion.dataentrydate). It is difficult to judge which one is better by the dates themselves due to data entry might have been done quite later than Date of Information. For Afghanistan I had 234 missing links and then comparing the two options 211 were the same. I decided that Min(fieldreport.dateofreport) was the best which I also used for South Sudan.		The difficulty is to how to identify which link row to transform to ''NEW''. I have tried Min(fieldreport.dateofreport) and Min(hazardinfoversion.dataentrydate). It is difficult to judge which one is better by the dates themselves due to data entry might have been done quite later than Date of Information. For Afghanistan I had 234 missing Land links and then comparing the two options 211 were the same. I decided that Min(fieldreport.dateofreport) was the best options which I also used for South Sudan. But for missing Activity links the best option was Min(hazreducinfoversion.dataentrydate) for Afghanistan.
	<pre>		<pre>
	UPDATE link		UPDATE link

Alnaucler at 12:28, 20 July 2017

2017-07-20T12:28:50Z

← Older revision		Revision as of 14:28, 20 July 2017
Line 270:		Line 270:
	"public".link.update_type <> 'UPDATE'		"public".link.update_type <> 'UPDATE'
	</pre>		</pre>

			=== Infoversion missing ===__NOEDITSECTION__
			We have also had cases where the infoversion records was missing and in these cases
			* fieldreport
			* infoversion
			* cdfvalues
			* geodata
			* multi-select
			* links
			have to be recreated.
	[[Category:SQL Queries]]		[[Category:SQL Queries]]
	[[Category:NoPublic]]		[[Category:NoPublic]]
	{{NavBox Hub}}		{{NavBox Hub}}

Alnaucler at 10:20, 20 July 2017

2017-07-20T10:20:46Z

← Older revision		Revision as of 12:20, 20 July 2017
Line 230:		Line 230:

	=== UPDATE other than Location example ===__NOEDITSECTION__		=== UPDATE other than Location example ===__NOEDITSECTION__
	~~Note in this example~~ I ~~use~~ Min(~~"public"~~.fieldreport.dateofreport) ~~which~~ was the best ~~option in~~ South Sudan ~~(another option would be hazardinfoversion.dataentrydate)~~.		The difficulty is to how to identify which link row to transform to ''NEW''. I have tried Min(fieldreport.dateofreport) and Min(hazardinfoversion.dataentrydate). It is difficult to judge which one is better by the dates themselves due to data entry might have been done quite later than Date of Information. For Afghanistan I had 234 missing links and then comparing the two options 211 were the same. I decided that Min(fieldreport.dateofreport) was the best which I also used for South Sudan.
	<pre>		<pre>
	UPDATE link		UPDATE link

Alnaucler at 09:24, 20 July 2017

2017-07-20T09:24:50Z

← Older revision		Revision as of 11:24, 20 July 2017
Line 230:		Line 230:

	=== UPDATE other than Location example ===__NOEDITSECTION__		=== UPDATE other than Location example ===__NOEDITSECTION__
	Note in this example I use Min("public".fieldreport.dateofreport) which was the best option in South Sudan (~~third~~ option would be hazardinfoversion.dataentrydate).		Note in this example I use Min("public".fieldreport.dateofreport) which was the best option in South Sudan (another option would be hazardinfoversion.dataentrydate).
	<pre>		<pre>
	UPDATE link		UPDATE link

Alnaucler at 09:20, 20 July 2017

2017-07-20T09:20:39Z

← Older revision		Revision as of 11:20, 20 July 2017
Line 182:		Line 182:

	=== UPDATE Location rows===__NOEDITSECTION__		=== UPDATE Location rows===__NOEDITSECTION__
	It are 5 fields that needs to be updated for rows that should be transformed into NEW_LOCATION rows		There are 5 fields that needs to be updated for rows that should be transformed into NEW_LOCATION rows
	# approval_state, should be NEW_LOCATION		# approval_state, should be NEW_LOCATION
	# to_guid, should be same as from_guid		# to_guid, should be same as from_guid

Alnaucler at 13:15, 5 June 2017

2017-06-05T13:15:28Z

← Older revision		Revision as of 15:15, 5 June 2017
Line 189:		Line 189:
	# update_type, should be NULL		# update_type, should be NULL

	This query identifies which rows to update. I put them in a temporary table ''new_link_loc''. Note in this example I use Min("public".link.data_entry_date) which was the best option in South Sudan.		This query identifies which rows to update. I put them in a temporary table ''new_link_loc''. Note in this example I use Min("public".link.data_entry_date) which was the best option in South Sudan.
			{{Note\| If you export out to Excel and then back in to the database as temporary table then you must define the column ''min'' as yyyy-mm-dd hh:mm:ss in Excel in order to preserve the seconds.}}
	<pre>		<pre>
	SELECT		SELECT

Alnaucler at 22:07, 17 April 2017

2017-04-17T22:07:56Z

← Older revision		Revision as of 00:07, 18 April 2017
Line 189:		Line 189:
	# update_type, should be NULL		# update_type, should be NULL

	This query identifies which rows to update. I put them in a temporary table ''new_link_loc''.		This query identifies which rows to update. I put them in a temporary table ''new_link_loc''. Note in this example I use Min("public".link.data_entry_date) which was the best option in South Sudan.
	<pre>		<pre>
	SELECT		SELECT
Line 228:		Line 228:
	</pre>		</pre>

	=== UPDATE example ===__NOEDITSECTION__		=== UPDATE other than Location example ===__NOEDITSECTION__
			Note in this example I use Min("public".fieldreport.dateofreport) which was the best option in South Sudan (third option would be hazardinfoversion.dataentrydate).
	<pre>		<pre>
	UPDATE link		UPDATE link
Line 240:		Line 241:
	"public".hazardinfoversion		"public".hazardinfoversion
	INNER JOIN "public".hazard ON "public".hazardinfoversion.hazard_guid = "public".hazard.hazard_guid		INNER JOIN "public".hazard ON "public".hazardinfoversion.hazard_guid = "public".hazard.hazard_guid
			INNER JOIN "public".fieldreport ON "public".hazardinfoversion.fieldreport_guid = "public".fieldreport.fieldreport_guid
	WHERE		WHERE
	"public".hazardinfoversion.hazardinfoversion_guid = "public".link.from_guid AND		"public".hazardinfoversion.hazardinfoversion_guid = "public".link.from_guid AND
	(hazardinfoversion.hazard_guid, ~~hazardinfoversion~~.~~dataentrydate~~) IN ((SELECT		(hazardinfoversion.hazard_guid, "public".fieldreport.dateofreport) IN ((SELECT
	"public".hazard.hazard_guid AS guid,		"public".hazard.hazard_guid AS guid,
	Min("public".~~hazardinfoversion~~.~~dataentrydate~~)		Min("public".fieldreport.dateofreport)
	FROM		FROM
	"public".hazard		"public".hazard
	INNER JOIN "public".hazardinfoversion ON "public".hazardinfoversion.hazard_guid = "public".hazard.hazard_guid		INNER JOIN "public".hazardinfoversion ON "public".hazardinfoversion.hazard_guid = "public".hazard.hazard_guid
	INNER JOIN "public".link ON "public".link.from_guid = "public".hazardinfoversion.hazardinfoversion_guid		INNER JOIN "public".link ON "public".link.from_guid = "public".hazardinfoversion.hazardinfoversion_guid
			INNER JOIN "public".fieldreport ON "public".fieldreport.fieldreport_guid = "public".hazardinfoversion.fieldreport_guid
	WHERE		WHERE
	"public".hazardinfoversion.link_only = 'f' AND		"public".hazardinfoversion.link_only = 'f' AND
Line 259:		Line 262:
	from link		from link
	where approval_state = 'NEW'		where approval_state = 'NEW'
	and link.from_guid = hazardinfoversion.hazardinfoversion_guid		and link.from_guid = hazardinfoversion.hazardinfoversion_guid ) )
	)
	)
	GROUP BY		GROUP BY
	"public".hazard.hazard_guid))		"public".hazard.hazard_guid))

Alnaucler at 21:08, 17 April 2017

2017-04-17T21:08:19Z

@@ Line 179: / Line 179: @@
 ORDER BY
 "public".qa.qa_localid ASC
 </pre>

Alnaucler at 17:45, 9 November 2016

2016-11-09T17:45:53Z

← Older revision		Revision as of 19:45, 9 November 2016
Line 23:		Line 23:

	==Case Combine==__NOEDITSECTION__		==Case Combine==__NOEDITSECTION__
			[[Image:Link error4.png\|1000px\|center]]<br/>

	The solution is actually '''not''' to add rows to the table ''link'' but to update some columns of existing rows.		The solution is actually '''not''' to add rows to the table ''link'' but to update some columns of existing rows.
	<ol>		<ol>