Andrew T Johnson 3leftturns

Andrew T Johnson Web Design
Salt Lake City, UT
http://www.andrewtjohnson.me

Recently created

Least recently created

Recently updated

Least recently updated

3leftturns / PhraseOMatic.java

Created August 21, 2014 19:04

The Phrase-O-Matic from Head First Java. My first Java app! Compile and run. It gives pervasive 30,000 foot buzzphrases.

	public class PhraseOMatic {
	public static void main (String[] args) {
	String[] wordListOne = {
	"24/7",
	"multi-Tier",
	"30,000 foot",
	"B-to-B",
	"win-win",
	"front-end",
	"web-based",

3leftturns / Second Substring

Created July 11, 2014 20:34

Excel return value of second search character in substring

	This is the text to search, in cell A1:
	Andy Is Awesome

	Use the =FIND function to get the index of the first space:
	=FIND(<text to search for>, <text to search in>, <index to start at>)

	=FIND(" ", A1)

	Returns 5

3leftturns / sum and group by sql

Created June 13, 2014 16:55

Got a list of sales in a table and need to summarize total sales for each sales agent? This GIST can help. Selects sales agent and sums their sales, then groups into 1 line per sales agent.

	SELECT salesagent, SUM(saleamount) as salestotal
	FROM sales.salestable
	GROUP BY salesagent;

3leftturns / SQL IDENTITY workaround code

Created June 4, 2014 03:19

SQL: A very painful way to get around using the IDENTITY constraint to create a unique primary key. Lesson learned: use IDENTITY when designing the table that needs an autogenerated unique integer PK.

	BEGIN TRANSACTION
	--Declare variable to hold max primary key value
	DECLARE @priKey int =
	(SELECT MAX(PK)
	FROM normalSales) + 1

	--INSERT values into table
	INSERT INTO normalSales(PK, saleDate, saleAmount, city, saleQuantity, saleTotal)
	VALUES (
	@priKey,

3leftturns / SQL pivot query

Created May 28, 2014 01:58

SQL collapse columns to rows. Got a crappy database that has sales data in columns by city tallied up in each column? This will collapse the column into 4 columns: saleDate, saleAmount, city, saleQuantity. See the alternate file for a visual.

	SELECT salesTable.saleDate, salesTable.saleAmount, 'Reno' AS city, salesTable.reno AS saleQuantity
	FROM salesTable
	WHERE salesTable.reno > 0

	UNION

	SELECT salesTable.saleDate, salesTable.saleAmount, 'Salt Lake City' AS city, salesTable.saltLakeCity AS saleQuantity
	FROM salesTable
	WHERE salesTable.saltLakeCity > 0

3leftturns / Address Parse

Created May 17, 2014 19:48

SQL select and parse address records from 1 column into 3: house number, cardinal direction, street address. Only works for fixed width addresses in format HHHH D AAAAAAAAAAAAAAAAAAAAA where H = house number, D = direction, and A = address. Address can be as long as needed.

	SELECT SUBSTRING(address, 1, 4) AS houseNumber, SUBSTRING(address, 6, 2) AS cardinalDirection, SUBSTRING(address, 7, (LEN(address)-6)) AS streetAddress
	FROM contactList