Category:BikeSaviours - Revision history

RobertBushman: /* Non-Signup Linked Update */

2021-12-05T10:29:44Z

Non-Signup Linked Update

← Older revision		Revision as of 10:29, 5 December 2021
Line 88:		Line 88:

	'''''WARNING: High Voltage: Validate Data Extremely Carefully'''''		'''''WARNING: High Voltage: Validate Data Extremely Carefully'''''

	There is a high risk of duplicate short names causing data corruption. Validate this data with extreme scrutiny, armed with a knowledge of recent volunteers.		There is a high risk of duplicate short names causing data corruption. Validate this data with extreme scrutiny, armed with a knowledge of recent volunteers.

RobertBushman: /* Updating Sign-Ins */

2021-12-05T10:29:27Z

Updating Sign-Ins

← Older revision		Revision as of 10:29, 5 December 2021
Line 85:		Line 85:
	</pre>		</pre>

	=== ~~Show Proposed~~ Non-Signup ~~Links~~ ===		=== Non-Signup Linked Update ===

	'''''WARNING: High Voltage: Validate Data Extremely Carefully'''''		'''''WARNING: High Voltage: Validate Data Extremely Carefully'''''
Line 91:		Line 91:

	<pre>		<pre>
	select i.shop_date, i.short_name, i.email, n.email, n.first_name, n.last_name~~, n.time_range~~		-- Non-Signup-Linked Update, All Dates (Dangerous) --
			select i.shop_date, i.short_name, i.email, s.email, v.first_name, v.last_name
	from bsbc.staff_sign_in i		from bsbc.staff_sign_in i
	join bsbc.~~staff_sign_up_name n~~ on n.~~shop_date~~ = i.~~shop_date~~		join bsbc.staff_short_name s on s.short_name = i.short_name
	join bsbc.~~staff_short_name s~~ on s.email = n.email ~~and s.short_name = i.short_name~~		join bsbc.volunteer v on v.email = s.email
	where i.email is null		where i.email is null
			and substr(i.short_name, 1) = substr(s.short_name, 1)
	;		;
	</pre>		</pre>

	~~=== Update Non-Signup Links ===~~		'''''WARNING WARNING: Seriously, Do Not Run This Unless You Have Checked Carefully'''''
	'''''WARNING: ~~High Voltage: Validate Data Extremely~~ Carefully'''''
	~~There is a high risk of duplicate short names causing data corruption. Validate this data with extreme scrutiny, armed with a knowledge of recent volunteers.~~

	<pre>		<pre>
	update bsbc.staff_sign_in i		update bsbc.staff_sign_in i
	~~join bsbc.staff_sign_up_name n on n.shop_date = i.shop_date~~		join bsbc.staff_short_name s on s.short_name = i.short_name
	join bsbc.staff_short_name s on ~~s.email = n.email and~~ s.short_name = i.short_name		set i.email = s.email
	set i.email = n.email		where i.email is null
	;		;
	</pre>		</pre>

RobertBushman: /* Create Short Name Links */

2021-12-05T10:17:26Z

Create Short Name Links

@@ Line 82: / Line 82: @@
 set i.email = n.email
 where i.shop_date = @date
+;
 </pre>

RobertBushman: /* Find Dates Missing Assignments */

2021-12-05T10:11:13Z

Find Dates Missing Assignments

← Older revision		Revision as of 10:11, 5 December 2021
Line 33:		Line 33:
	select shop_date, count(1) num_staff from bsbc.staff_sign_in		select shop_date, count(1) num_staff from bsbc.staff_sign_in
	where email is null group by shop_date order by num_staff asc;		where email is null group by shop_date order by num_staff asc;
			</pre>

			=== Set Target Date ===

			<pre>
			set @date = '2021-12-02';
	</pre>		</pre>

RobertBushman: /* Updating Sign-Ins */

2021-12-05T10:10:05Z

Updating Sign-Ins

← Older revision		Revision as of 10:10, 5 December 2021
Line 27:		Line 27:
	# > set @date = '2021-12-02';		# > set @date = '2021-12-02';
	# > select i.shop_date, i.short_name, i.email from bsbc.staff_sign_in i where i.shop_date = @date;		# > select i.shop_date, i.short_name, i.email from bsbc.staff_sign_in i where i.shop_date = @date;

			=== Find Dates Missing Assignments ===

			<pre>
			select shop_date, count(1) num_staff from bsbc.staff_sign_in
			where email is null group by shop_date order by num_staff asc;
			</pre>

	=== Find Possible Matches ===		=== Find Possible Matches ===

RobertBushman: /* Add Short Name Link */

2021-12-05T10:08:09Z

Add Short Name Link

@@ Line 58: / Line 58: @@
 ...
         ('shamus@bikesaviours.org', 'Shamus');
 </pre>

RobertBushman: /* Find Possible Matches */

2021-12-05T10:06:24Z

Find Possible Matches

@@ Line 37: / Line 37: @@
 and i.email is null
+;
 </pre>

RobertBushman: /* Updating Sign-Ins */

2021-12-05T09:54:30Z

Updating Sign-Ins

← Older revision		Revision as of 09:54, 5 December 2021
Line 27:		Line 27:
	# > set @date = '2021-12-02';		# > set @date = '2021-12-02';
	# > select i.shop_date, i.short_name, i.email from bsbc.staff_sign_in i where i.shop_date = @date;		# > select i.shop_date, i.short_name, i.email from bsbc.staff_sign_in i where i.shop_date = @date;

			=== Find Possible Matches ===

			<pre>
			select i.shop_date, i.short_name, i.email, n.email, n.first_name, n.last_name, n.time_range
			from bsbc.staff_sign_in i
			join bsbc.staff_sign_up_name n on n.shop_date = i.shop_date
			where i.shop_date = @date
			and i.email is null
			;
			</pre>

RobertBushman: /* Updating Sign-Ins */

2021-12-05T09:52:48Z

Updating Sign-Ins

← Older revision		Revision as of 09:52, 5 December 2021
Line 25:		Line 25:
	# > update bsbc.staff_sign_in i set i.email = 'intern' where i.short_name = 'Adam';		# > update bsbc.staff_sign_in i set i.email = 'intern' where i.short_name = 'Adam';
	# > -- this is dangerous		# > -- this is dangerous
	# > set @date = '2021-11-18';		# > set @date = '2021-12-02';
	# > select i.shop_date, i.short_name, i.email from bsbc.staff_sign_in i where i.shop_date = @date;		# > select i.shop_date, i.short_name, i.email from bsbc.staff_sign_in i where i.shop_date = @date;

RobertBushman: /* Updating Sign-Ins */

2021-12-05T09:52:32Z

Updating Sign-Ins

← Older revision		Revision as of 09:52, 5 December 2021
Line 25:		Line 25:
	# > update bsbc.staff_sign_in i set i.email = 'intern' where i.short_name = 'Adam';		# > update bsbc.staff_sign_in i set i.email = 'intern' where i.short_name = 'Adam';
	# > -- this is dangerous		# > -- this is dangerous
			# > set @date = '2021-11-18';
			# > select i.shop_date, i.short_name, i.email from bsbc.staff_sign_in i where i.shop_date = @date;