This is a list of my most common SQL statements I found useful (either auto generated by access or made manually) as well as their applications in creating a datawarehouse

SQL

1
2
SELECT DISTINCT VendorShortName
FROM dbo_products;

For my work software, I ran this to create a list of vendor shortname unique primary Keys so that it would match my 500,000 row data import. A list of 100 vendors + were made. Saves a lot of time and consistency errors

1
2
SELECT A.Model
FROM A INNER JOIN dbo_products ON A.Model = dbo_products.Model

basically, “A” table has entries to put in. Inner join for values common between both tables, extract results. Similar to VLOOKUP in excel

I use this to extract model# I put in and grab out things like list prices

1
2
SELECT A.Model, dbo_products.ListPrice
FROM A INNER JOIN dbo_products ON A.Model = dbo_products.Model;

Adding dbo_products.ListPrice adds in ability to grab list price values. Can add as many columns of data here as needed. Just one example

Excel

1
=TRIM(IFERROR(MID(E146,FIND(",",E146,FIND("HP",E146)-13)+1,(FIND(",",E146,FIND("HP",E146))-FIND(",",E146,FIND("HP",E146)-13))-1),""))

This formula abstracts a messily compiled list of spec data and seperates them out into their own field. From there that data could be processed but I never ventured further.

The formula basically stops if an error is found. It looks at the midpoint of the text string, using a reference parameter “HP” for horsepower, and backtracks an arbitrary number of steps until it finds a comma. It goes back and forth between commas until HP is found

Its a stupidly complicated formula though and I think that a python script is probably much more efficient

Its fairly similar to a binary search algorithm though

Resources

  • None

Vincent Tang

Comments