Clint Johnson clintmjohnson

My primary expertise is eCommerce Data. I am Proficient in ETL, SQL Server, SSIS, Python, Pandas, CSV, XML

clintmjohnson / filterduplicates.sql

Last active August 29, 2015 14:23

This Query Filters a SQL Server Table to only Dupicate Values

	select o.model_number, oc.model_number
	from Product_Staging_Table o
	inner join (
	SELECT model_number, COUNT(*) AS dupeCount
	FROM Product_Staging_Table
	GROUP BY model_number
	HAVING COUNT(*) > 1
	) oc on o.model_number = oc.model_number
	WHERE o.model_number <> '' AND o.model_number IS NOT NULL

clintmjohnson / MMDDYYY_2_SQLDate.sql

Created June 1, 2015 21:09

Convert MMDDYYYY to SQL Server Date Type Function

	CREATE FUNCTION [dbo].[MMDDYYYY_TO_SQL_DATE]
	(
	@MMDDYYYY char(8)
	)

	RETURNS date
	AS

	BEGIN

clintmjohnson / blazeexample.py

Created May 30, 2015 04:05

Python Blaze Example List Computation

	from blaze import *
	accounts = Symbol('accounts', 'var * {id: int, name: string, amount: int}')

	deadbeats = accounts[accounts.amount < 100].name

	L = [[1, 'Alice', 100],
	[2, 'Bob', -200],
	[3, 'Charlie', 300],
	[4, 'Denis', 400],
	[5, 'Edith', -500]]

clintmjohnson / UntangleExample.py

Created May 29, 2015 18:17

This is an XML Untangle Example

clintmjohnson / LastDayofMonth.sql

Created May 21, 2015 22:32

Determines the Last Day of a Month EOMONTH SQL SERVER

	SELECT MyData,
	EOMONTH(MyData) AS LastDayOfThisMonth,
	EOMONTH(MyData, 1) AS LastDayOfNextMonth
	FROM (VALUES ('2012-02-14T00:00:00' ),
	('2012-01-01T00:00:00'),
	('2012-12-31T23:59:59.9999999')) dt(MyData)

clintmjohnson / IsThisStringaDate.SQL

Created May 21, 2015 22:31

Determine whether a String is a Valid Date or Not SQL SERVER

clintmjohnson / channeladvisorordersapi.py

Created May 18, 2015 17:40

Channel Advisor / Python Get Orders API Script

	from suds.client import Client
	import logging

	logging.basicConfig(level=logging.INFO)
	logging.getLogger('suds.client').setLevel(logging.DEBUG)

	# Specify Login Information
	developer_key = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'
	password = 'xxxxxxxx'
	account_guid = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'

clintmjohnson / VerifyIfUPC.SQL

Last active October 13, 2015 17:32

This CASE Statement checks to see if a string is a Valid UPC Number or Not. - It verifies that the string has only Numbers, and its length is 12

	USE DatabaseNameHere
	SELECT
	-- This CASE Expression Verifies that the Value in the UPC Field is All Numbers and It's Total Length is 12
	,CASE WHEN ISNUMERIC(UPC) = 1 AND LEN([UPC]) = 12 THEN [UPC]
	WHEN ISNUMERIC(UPC) = 1 AND LEN([UPC]) = 11 THEN '0'+[UPC]
	ELSE '' END AS [UPC]
	FROM dbo.YourTableNameHere

clintmjohnson / RemoveLeft2SQL.SQL

Last active August 29, 2015 14:21

This Select Query removes the first 2 Characters from the left of a String

,RIGHT(columnname, LEN(columnname) - 2) AS MyTrimmedColumn

clintmjohnson / REPLACEPhone.SQL

Last active August 29, 2015 14:21

This is a Nested REPLACE Query for Stripping non numerical values from Phone Numbers.